触发器trigger_before_after_for each row_when_case(一）

--case when之inserting_updating_deleting 行级(for each row)触发器(非语句级)语法
create or replace trigger tri_test
before insert or update or delete on dept
for each row
begin
 case
   when inserting then
      dbms_output.put_line('inserting a row');
   when updating then
      dbms_output.put_line('updating a row');
   when deleting then
      dbms_output.put_line('deleting a row');
 end case;
end;
/

 

SQL> update dept set loc=lower(loc);--触发触发器
updating a row
updating a row
updating a row
updating a row

4 rows updated.

SQL> insert into dept values(50,'IT','beijing');--触发触发器
inserting a row

1 row created.

 

 

 

---加上when过滤条件(when用于仅触发符合when条件）的行级触发器示例
SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

create or replace trigger tri_test
before insert or update or delete on test --test为dept复制表
for each row
when(old.deptno=10 and new.deptno=88 or new.loc in('BOSTON','CHICAGO')) --when语法位于before及for each row之后,且唯有符合此符件才会触发触发器,注意里面用到了关键字, old及new;old及new用and连接，用于update动作
                                                                        --when语法是布尔表达式
begin
 case
   when inserting then
      dbms_output.put_line('inserting a row'||:new.deptno||','||:new.dname||','||:new.loc);--插入记录时，打印插入的新值,采用了关键字:new及:old
   when updating then
      dbms_output.put_line('updating a row'||:old.deptno||','||:new.deptno); --更新记录时，打印旧值及新值
   when deleting then
      dbms_output.put_line('deleting a row');
 end case;
end;
/

SQL> update test set deptno=88 where deptno=10;---符合when条件，得以触发
updating a row10,88

1 row updated.

SQL> insert into test values(55,'it','beijing'); --不符合when条件，不触发

1 row created.

SQL> rollback;

Rollback complete.

SQL> delete from test;

4 rows deleted.

SQL> rollback;

Rollback complete.

SQL> insert into test values(55,'it','CHICAGO');--符合when，触发
inserting a row55,it,CHICAGO

1 row created.

SQL> insert into test values(55,'it','CHICAGO1');--不符合when,不触发

1 row created.

SQL> rollback;

Rollback complete.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-670944/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-670944/