触发器

把这些例子都亲自敲一遍，Oracle触发器就应该了解的差不多了




引用：http://www.qiyeku.com/xinwen/846342.html



--格式：

create or replace trigger 名称

[after|before][delete|update|insert]

[on 表 | on schema]

[referencing new as 别名 old as 别名]

[for each row]

declare

  ....

begin

...

exception

....

end;



--创建一个delete类型行级触发器

--删除的一行数据保存在:old

create or replace trigger emp_delete_row_trigger

after delete

on emp

referencing new as n old as o

for each row

begin

  dbms_output.put_line('emp_delete_row_trigger called.');

  dbms_output.put_line('删除员工：' ||.empno || ' ' ||.ename);

end;



delete from emp where empno=7499;

delete from emp;

--创建一个insert类型行级触发器

--插入的一行新数据保存在:new

create or replace trigger emp_insert_row_trigger

after insert

on emp

for each row

begin

  dbms_output.put_line('emp_insert_row_trigger called.');

  dbms_output.put_line('添加员工：' || :new.empno || ' ' || :new.ename);

end;



insert into emp(empno,ename) values(1,'empxxx');



--创建一个update类型行级触发器

--修改前的数据保存在:old

--修改后的数据保存在:new

create or replace trigger emp_update_row_trigger

after update

on emp

for each row

begin

  dbms_output.put_line('emp_update_row_trigger called.');

  dbms_output.put_line('修改前：' ||ld.empno || ' ' ||ld.ename);

  dbms_output.put_line('修改后：' || :new.empno || ' ' || :new.ename);

end;



update emp

set ename='xxxx'

where empno=7499;



--语句级触发器(update,delete,insert)

create or replace trigger delete_stmt_trigger

after delete

on emp

begin

  dbms_output.put_line('delete_stmt_trigger called.');

end;



delete from emp;

--判断触发器类型



----------------------------------------------------------

--每进行一次交易，就要调用触发器，自动扣除或增加账户金额

----------------------------------------------------------

create table account

(

  customerName varchar2(30) primary key,

  cardID varchar2(8),

  currentMoney number

);

insert into account values('Daivd','10010001',5000);

insert into account values('Jason','10010002',3000);



create table trans

(

  transDate date,

  cardID varchar2(8),

  transType varchar2(10),

  transMoney number

);

insert into trans

values(sysdate,'10010001','取款',1000);



create or replace trigger trans_trigger

before insert

on trans

for each row

declare

  v_currentMoney account.currentMoney%type;

begin

  --判断类型

  if :new.transType='取款' then

   --取款

   select currentMoney into v_currentMoney

   from account

   where cardID=:new.cardID;

  

   if v_currentMoney