create [orreplace] trriger tri_name [before|after|instead of] tri_event on table_name|view_name|user_name|db_name [foreachrow][when tri_contidion] begin plsql_sentences; end tri_name;
4.分析
before | after | instead of
描述触发时机,before,after分别在dml触发之前、之后。
before 便于防止某些错误操作发生而便于回滚事务或实现某些业务规则。
after 便于记录该操作或做某些事件后处理信息。
instead of 表示为 替代触发器。
on
表示操作的数据表、视图、用户模式(schema)和数据库(database)。
for each row
指定为行级触发器,不指定就是语句触发器(无论什么情况,plsql_sentences都只执行一次)
tri_event
触发事件 如insert 、update、delete、create、alter、drop等。
tri_condition
触发条件表达式 只有该表达式为true时才会触动触发器。
plsql_sentences中的条件谓词
plsql_sentences执行语句块,条件谓词有 inserting 、updating、deleting等,表示在处理语句块中对以上三种操作 的分类执行对应语句。
书写方式 : if inserting then …;elsif updating then …;
当然:条件谓词还可以判断 正在对是否某一个特定列操作。
如: if updating(dname) then
CREATEORREPLACETRIGGER triger2 BEFOREINSERTORDELETEORUPDATE ON dept BEGIN IF (TO_CHAR(sysdate,'DAY') NOTIN ('星期六', '星期日')) AND (TO_CHAR(sysdate, 'HH24:MI') BETWEEN'08:00'AND'18:00') THEN RAISE_APPLICATION_ERROR(-20001, '上班时间,不能修改dept表'); ENDIF; END; --测试(注意观察错误信息与查询结果): UPDATE dept SET loc = 'Shanghai'WHERE deptno = 20;
CREATEORREPLACETRIGGER trigger3 BEFOREUPDATEOF sal, comm ORDELETE ON emp FOREACHROW WHEN (old.deptno = 30) BEGIN CASE WHEN UPDATING ('sal') THEN IF :new.sal < :old.sal THEN RAISE_APPLICATION_ERROR(-20001, '部门30的人员的工资不能降'); ENDIF; WHEN UPDATING ('comm') THEN IF :new.comm < :old.comm THEN RAISE_APPLICATION_ERROR(-20002, '部门30的人员的奖金不能降'); ENDIF; WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003, '不能删除部门30的人员记录'); ENDCASE; END; --测试(注意观察错误信息与查询结果): UPDATE emp SET sal = 8000WHERE empno = 7499;
DELETEFROM emp WHERE emp in (7499,7369); ALTERTRIGGER trigger3 DISABLE; DELETEFROM emp WHERE emp in (7499,7369); ROLLBACK;
CREATEORREPLACETRIGGER trigger4 AFTERupdateOF deptno ON dept FOREACHROW BEGIN DBMS_OUTPUT.PUT_LINE('旧的deptno值是'||:old.deptno||'、新的deptno值是'||:new.deptno); UPDATE emp SET deptno = :new.deptno WHERE deptno= :old.deptno; END; --测试(注意观察错误信息与查询结果): UPDATE dept SET loc = 'Shanghai'WHERE deptno = 20;
DELETEFROM dept WHERE deptno = 20; UPDATE dept SET deptno = 70WHERE deptno = 30; SELECT * FROM dept ; SELECT * FROM emp WHERE deptno = 30; SELECT * FROM emp WHERE deptno = 70; ROLLBACK; ALTERTRIGGERtrigger4DISABLE; UPDATE dept SET deptno = 70WHERE deptno = 30; SELECT * FROM dept ; SELECT * FROM emp WHERE deptno = 30; SELECT * FROM emp WHERE deptno = 70; ROLLBACK;
例5:Instead of 触发器。
--新建视图: CREATEORREPLACEVIEW emp_view AS SELECT deptno, count(*) total_employeer, sum(sal) total_salary FROM emp GROUPBY deptno; --测试(注意观察错误信息): DELETEFROM emp_view WHERE deptno=10;
--创建Instead of 触发器(instead of 与行级触发器) CREATEORREPLACETRIGGER trigger5 INSTEAD OFDELETEON emp_view FOREACHROW BEGIN DELETEFROM emp WHERE deptno= :old.deptno; END ;
--测试(注意观察错误信息与查询结果): DELETEFROM emp_view WHERE deptno=10;