oracle触发器例子 

 1 --建表：
 2 create table zhidao_20131014_tab2_1
 3 (
 4        HEAD_ID  varchar2(10),
 5        LINE_ID  varchar2(10),
 6        QTY  number
 7 );
 8 create table zhidao_20131014_tab2_2
 9 (
10        HEAD_ID  varchar2(10),
11        SUMQTY  number
12 );
13 --造数：
14 insert into zhidao_20131014_tab2_1
15 select 'H1','1',100 from dual
16 union all
17 select 'H1','2',200 from dual
18 union all
19 select 'H1','2',300 from dual
20 union all
21 select 'H1','2',100 from dual;
22  
23 insert into zhidao_20131014_tab2_2
24 select 'H1',700 from dual;
25 commit;
26 --触发器：
27 create or replace trigger tr_zhidao
28 after insert or update or delete
29 on zhidao_20131014_tab2_1
30 for each row
31 begin
32   case
33     when updating then
34       update zhidao_20131014_tab2_2 t set SUMQTY=SUMQTY - :o1ld.QTY + :new.QTY where t.head_id=:new.head_id;
35     when inserting then
36       update zhidao_20131014_tab2_2 t set SUMQTY=SUMQTY + :new.QTY where t.head_id=:new.head_id;
37     when deleting then
38       update zhidao_20131014_tab2_2 t set SUMQTY=SUMQTY - :old.QTY where t.head_id=:old.head_id;
39   end case;
40 end;
41 --测试：
42 --insert
43 insert into zhidao_20131014_tab2_1 
44 select 'H1','3',300 from dual;
45 --delete
46 delete zhidao_20131014_tab2_1 where HEAD_ID='H1' and LINE_ID='3';
47 --update
48 update zhidao_20131014_tab2_1 set QTY=1000 where QTY=300;

 

posted @ 2017-12-15 15:11  zyx-  阅读(315)  评论(0)  编辑  收藏  举报