Oracle:触发器
说明:oracle触发器,对指定表进行增删改操作,触发逻辑操作。
SQL> create or replace trigger agreement_trigger
2 after update or insert or delete on base_agreement_template_test
3 for each row
4 begin
5 if inserting then
6 insert into base_agreement_template_bak
7 values
8 (:new.AGREEMENT_NO,
9 :new.AGREEMENT_NAME,
10 :new.UPDATE_TIME);
11 elsif deleting then
12 delete from base_agreement_template_bak where AGREEMENT_NO = :old.AGREEMENT_NO;
13 else
14 update base_agreement_template_bak
15 set AGREEMENT_NO = :new.AGREEMENT_NO,
16 AGREEMENT_NAME = :new.AGREEMENT_NAME,
17 UPDATE_TIME = :new.UPDATE_TIME
18 where AGREEMENT_NO = :old.AGREEMENT_NO;
19 end if;
20 end;
21 /
Trigger created
SQL> select * from base_agreement_template_test;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
协议 借款协议 2017/7/10 1
test4 test4 2017/7/3 16
test1 test1 2017/7/3 15
test2 test2 2017/7/3 15
test3 test3 2017/7/3 16
test5 test5 2017/7/14 1
6 rows selected
SQL> select * from base_agreement_template_bak;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
登录触发:
SQL> insert into base_agreement_template_test values('test6', 'test6', sysdate);
1 row inserted
SQL> select * from base_agreement_template_test;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
协议 借款协议 2017/7/10 1
test4 test4 2017/7/3 16
test1 test1 2017/7/3 15
test2 test2 2017/7/3 15
test3 test3 2017/7/3 16
test5 test5 2017/7/14 1
test6 test6 2017/7/14 1
7 rows selected
SQL> select * from base_agreement_template_bak;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
test6 test6 2017/7/14 1
更新触发:
SQL> update base_agreement_template_test set AGREEMENT_NAME = 'test6_UPDATE' WHERE AGREEMENT_NO = 'test6';
1 row updated
SQL> select * from base_agreement_template_test;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
协议 借款协议 2017/7/10 1
test4 test4 2017/7/3 16
test1 test1 2017/7/3 15
test2 test2 2017/7/3 15
test3 test3 2017/7/3 16
test5 test5 2017/7/14 1
test6 test6_UPDATE 2017/7/14 1
7 rows selected
SQL> select * from base_agreement_template_bak;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
test6 test6_UPDATE 2017/7/14 1
删除触发:
SQL> delete from base_agreement_template_test where agreement_no = 'test6';
1 row deleted
SQL> select * from base_agreement_template_test;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
协议 借款协议 2017/7/10 1
test4 test4 2017/7/3 16
test1 test1 2017/7/3 15
test2 test2 2017/7/3 15
test3 test3 2017/7/3 16
test5 test5 2017/7/14 1
6 rows selected
SQL> select * from base_agreement_template_bak;
AGREEMENT_NO AGREEMENT_NAME UPDATE_TIME
------------ ---------------------------------------------------------------- -----------
删除触发器:
SQL> drop trigger agreement_trigger;
Trigger dropped