触发器

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


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


--格式：

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('删除员工：' || :o.empno || ' ' || :o.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('修改前：' || :old.empno || ' ' || :old.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