BEFORE INSERT BEFORE INSERT FOR EACH ROW AFTER INSERT AFTER INSERT FOR EACH ROW BEFORE UPDATE BEFORE UPDATE FOR EACH ROW AFTER UPDATE AFTER UPDATE FOR EACH ROW BEFORE DELETE BEFORE DELETE FOR EACH ROW AFTER DELETE AFTER DELETE FOR EACH ROW
触发器执行顺序
执行 BEFORE语句级触发器
对于受语句影响的每一行：
执行 BEFORE行级触发器
执行 DML语句
执行 AFTER行级触发器
执行 AFTER语句级触发器
创建DML触发器
触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字
但在一个模式中触发器名不能相同
示例
1 2 3 4 5 6 7 8
--例： 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。 CREATE OR REPLACE TRIGGER del_emp BEFORE DELETE ON scott.emp FOR EACH ROW BEGIN --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 INSERT INTO emp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate)VALUES (:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal, :old.comm,:old.hiredate); END;
CREATE OR REPLACE TRIGGER upd_emp BEFORE update ON scott.emp REFERENCING new AS nn old AS oo FOR EACH ROW WHEN (nn.sal > 2000) BEGIN dbms_output.put_line(:nn.sal||'------'||:oo.sal); END;
DML触发器中的谓词
在DML触发器中，他们被不同的DML语句所触发，有三个布尔型函数来确定操作到底是什么：
谓词
描述
INSERTING
如果触发语句是INSERT，则为TRUE，否则为FALSE
UPDATING
如果触发语句是UPDATE，则为TRUE，否则为FALSE
DELETING
如果触发语句是DELETE，则为TRUE，否则为FALSE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
--例 CREATE OR REPLACE TRIGGER check_emp BEFORE update OR insert OR delete ON scott.emp REFERENCING new AS nn old AS oo FOR EACH ROW WHEN (nn.sal > 2000) BEGIN IF INSERTING THEN dbms_output.put_line('THE OPERATION IS INSERT'); ELSIF UPDATING THEN dbms_output.put_line('THE OPERATION IS UPDATE'); ELSIF DELETING THEN dbms_output.put_line('THE OPERATION IS DELETE'); ELSE dbms_output.put_line('OTHERS OPERATION'); END IF; END;
创建替代触发器
语法
1 2 3 4 5 6 7
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF {INSERT | DELETE | UPDATE [OF column [, column …]]} ON [schema.] view_name [REFERENCING {OLD [AS] old | NEW [AS] new}] [FOR EACH ROW ] trigger_body;
CREATE OR REPLACE TRIGGER emp_view_delete INSTEAD OF DELETE ON emp_view FOR EACH ROW BEGIN DELETE FROM emp WHERE deptno= :old.deptno; END emp_view_delete; DELETE FROM emp_view WHERE deptno=10;
CREATE OR REPLACE TRIGGER trig4_ddl AFTER CREATE OR ALTER OR DROP ON DATABASE DECLARE Event VARCHAR2(20); Typ VARCHAR2(20); Name VARCHAR2(30); Owner VARCHAR2(30); BEGIN --读取DDL事件属性 Event := SYSEVENT; --激活触发器的事件名称 Typ := DICTIONARY_OBJ_TYPE; --语句所操作的数据库对象类型 Name := DICTIONARY_OBJ_NAME; --语句所操作的数据库对象名称 Owner := DICTIONARY_OBJ_OWNER; --语句所操作的数据库对象所有者名称 -- 将事件属性插入到事件日志表中 INSERT INTO scott.eventlog(eventname,obj_type,obj_name,obj_owner) VALUES(event, typ, name, owner); END;