1、第8章 触发器,8.1 触发器的种类和触发事件 8.2 DML触发器 8.3 数据库事件触发器 8.4 DDL事件触发器 8.5 替代触发器 8.6 查看触发器,触发器,触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。,8.1 触发器的种类和触发事件,触发器必须由事件才能触发。触发器的触发事件分可为3类，
2、分别是DML事件、DDL事件和数据库事件。 每类事件包含若干个事件，如表1所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。,表1 触发器事件,触发器的类型可划分为4种：数据操纵语言(DML)触发器、替代(INSTEAD OF)触发器、数据定义语言(DDL)触发器和数据库事件触发器。 各类触发器的作用如表2所示。,表2 触发器,8.2 DML触发器,8.2.1 DML触发器的要点DML触发器是定义在表上的触发器，由DML事件引发。编写DML触发器的要素是：(1)确定触发的表，即在其上定义触发器的表。(2)确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE
3、三种，说明见表1。,(3)确定触发时间。触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。(4)确定触发级别，有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。,由于在同一个表上可以定义多个DML触发器，因此触发器本身和引发触发器的SQL语句在执行的顺序上有先后的关系。它们的顺序是： 如果存在语句级BEFORE触发器，则先执行一次语句级BEFORE触发器。 在SQL语句的执行过程中，如果存在行级BEFORE触发器，则SQL语句在对每一行操作之前，都要先执行一次行级BEFO
4、RE触发器，然后才对行进行操作。如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。 如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。,触发器的执行顺序, 如果有，最先执行语句级before触发器。 每个insert，delete,update影响的行； 如果有，最先执行行级before； 执行行的delete或update； 如果有,执行行级after触发器； 如果有,执行语句级after触发器。,DML触发器还有一些具体的问题，说明如下： 如果有多个触发器被定义成为相同时间、相同事件触发，且最后定
5、义的触发器是有效的，则最后定义的触发器被触发，其他触发器不执行。 一个触发器可由多个不同的DML操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。 在行级触发器中，用:new 和:old(称为伪记录)来访问数据变更前后的值。但要注意，INSERT语句插入一条新记录，所以没有:old记录，而DELETE语句删除掉一条已经存在的记录，所以没有:new记录。UPDATE语句既有:old记录，也有:new记录，分别代表修改前后的记录。引用具体的某一列的值的方法是：:old.字段名或:new.字
6、段名,触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。定义一个触发器时要考虑上述多种情况，并根据具体的需要来决定触发器的种类。,8.2.2 DML触发器的创建 创建DML触发器需要CREATE TRIGGER系统权限。创建DML触发器的语法如下： CREATE OR REPLACE TRIGGER 触发器名 BEFORE|AFTER|INSTEAD OF 触发事件1 OR 触发事件2. ON 表名 WHEN 触发条件 FOR EACH ROW DECLARE 声明部分 BEGIN 主体部分 END;,其中： OR REPL
7、ACE：表示如果存在同名触发器，则覆盖原有同名触发器。 BEFORE、AFTER和INSTEAD OF：说明触发器的类型。 WHEN 触发条件：表示当该条件满足时，触发器才能执行。 触发事件：指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。 对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：UPDATE OF 列名1，列名2. ON 表名：表示为哪一个表创建触发器。 FOR EACH ROW：表示触发器为行级触发器，省略则为语句级触发器。,触发器的创建者或具有DROP ANY TIRGGER系统权限的人才能删除触发器。删除触发器的语法
8、如下：DROP TIRGGER 触发器名可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。该命令语法如下：ALTER TRIGGER 触发器名 DISABLE|ENABLE其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。同存储过程类似，触发器可以用SHOW ERRORS 检查编译错误。,触发器的限制,创建触发器有以下限制： (1) 代码大小。触发器代码大小必须小于32K。 (2) 触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。但是，
9、对于系统触发器(system trigger)可以使用CREATE、ALTER、DROP TABLE和ALTER COMPILE语句。 (3) LONG、LONG RAW和LOB的限制： 不能插入数据到LONG或LONG RAW； 来自LONG或LONG RAW的数据可以转换成字符型(如char、varchar2)，但是不能超过32K； 使用LONG或LONG RAW不能声明变量； 在LONG或LONG RAW列中不能使用:NEW和:PARENT； 在LOB中的:NEW变量不能修改。 (4) 引用包变量的限制。如果UPDATE或DELETE语句检测到当前的UPDATE冲突，则Oracle执行R
10、OLLBACK到SAVEPOINT上并重新启动更新，这样可能需要多次才能成功。,8.2.3 行级触发器的应用在行级触发器中，SQL语句影响的每一行都会触发一次触发器，所以行级触发器往往用在对表的每一行的操作进行控制的场合。若在触发器定义中出现FOR EACH ROW子句，则为行级触发器。,8.2.3 行级触发器的应用,【例】假设XSCJ数据库中增加一新表XS_HIS，表结构和表XS相同，用来存放从XS表中删除的记录。创建一个触发器，当XS表被删除一行，把删除的记录写到日志表XS_HIS中。CREATE OR REPLACE TRIGGER del_xsBEFORE DELETE ON XS F
11、OR EACH ROWBEGININSERT INTO XS_HIS (XH,XM,ZYM,XB,CSSJ,ZXF,BZ)VALUES(:OLD.XH,:OLD.XM, :OLD.ZYM, :OLD.XB, :OLD.CSSJ,:OLD.ZXF, :OLD.BZ);END del_xs; OLD修饰访问操作完成前列的值，NEW修饰访问操作完成后列的值。,【例】利用触发器在数据库XSCJ的XS表执行插入、更新和删除3种操作后给 出相应提示。CREATE TRIGGER cue_xsAFTER INSERT OR UPDATE OR DELETE ON XS FOR EACH ROWDECLARE
12、Infor char(10);BEGINIF INSERTING THENInfor:=插入;ELSIF UPDATING THENInfor:=更新;ELSEInfor:=删除;END IF;INSERT INTO SQL_INFO VALUES(infor);END cue_xs;,【例】 创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对XS表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。 在创建触发器之前，需要先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：LOG_ID：操作记录的编号，数值型，
13、它是该表的主键，由序列自动生成。LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为dept表创建类似的触发器，同样将操作记录到该表。LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。,LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于xs表，主键是xh。LOG_DATE：操作的日期，日期型，取当前的系统时间。LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录orcl账户进行操作，在该字段中，记录账户名为orcl。,
14、步骤1：在SQL*Plus中登录orcl账户，创建如下的记录表LOGS：CREATE TABLE logs(LOG_ID NUMBER(10) PRIMARY KEY,LOG_TABLE VARCHAR2(10) NOT NULL,LOG_DML VARCHAR2(10),LOG_KEY_ID NUMBER(10),LOG_DATE DATE,LOG_USER VARCHAR2(15);执行结果：表已创建。,步骤2：创建一个LOGS表的主键序列LOGS_ID_SEQ：CREATE SEQUENCE logs_id_squ INCREMENT BY 1 START WITH 1 MAXVALUE
15、 9999999 NOCYCLE NOCACHE;执行结果：序列已创建。步骤3：创建和编译以下触发器：CREATE OR REPLACE TRIGGER DML_LOGBEFORE -触发时间为操作前DELETE OR INSERT OR UPDATE - 由三种事件触发ON xsFOR EACH ROW - 行级触发器BEGIN,IF INSERTING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,xs,INSERT,:new.xh,SYSDATE,USER);ELSIF DELETING THENINSERT INTO logs VAL
16、UES(logs_id_squ.NEXTVAL,xs,DELETE,:old.xh,SYSDATE,USER);ELSEINSERT INTO logs VALUES(logs_id_squ.NEXTVAL,xs,UPDATE,:new.xh,SYSDATE,USER);END IF;END;,说明：本例中在xs表上创建了一个由INSERT或DELETE或UPDATE事件触发的行级触发器，触发器的名称是LOG_EMP。对于不同的操作，记录的内容不同。本例中只插入了一条记录，如果用一条不带WHERE条件的UPDATE语句来修改所有学生的学分，则将逐行触发触发器。INSERT、DELETE和UPD
17、ATE都能引发触发器动作，在分支语句中使用INSERTING、DELETING和UPDATING来区别是由哪种操作引发的触发器动作。,在本例的插入动作中，LOG_ID字段由序列LOG_ID_SQU自动填充为1；LOGS表LOG_KEY_ID字段记录的是新插入记录的主键8001；LOD_DML字段记录的是插入动作INSERT；LOG_TABLE字段记录当前表名EMP；LOG_DATE字段记录插入的时间04年3月1日；LOG_USER字段记录插入者STUDENT。,【训练2】 创建一个行级触发器LOG_SAL，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。用WHEN条
18、件限定触发器。在创建触发器之前，需要先创建事件记录表LOGERR，该表用来对操作进行记录。该表的字段含义解释如下：NUM：数值型，用于记录序号。MESSAGE：字符型，用于记录错误信息。步骤1：在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGERR：CREATE TABLE logerr(NUM NUMBER(10) NOT NULL,MESSAGE VARCHAR2(50) NOT NULL);,执行结果：表已创建。步骤2：创建和编译以下触发器：CREATE OR REPLACE TRIGGER log_salBEFOREUPDATE OF salON empFOR EAC
19、H ROWWHEN (new.job=CLERK AND (ABS(new.sal-old.sal)200),DECLARE v_no NUMBER;BEGINSELECT COUNT(*) INTO v_no FROM logerr; INSERT INTO logerr VALUES(v_no+1,雇员|:new.ename|的原工资：|:old.sal|新工资：|:new.sal);END;执行结果：触发器已创建。,步骤3：在EMP表中更新记录：UPDATE emp SET sal=sal+550 WHERE empno=7788;UPDATE emp SET sal=sal+500 W
20、HERE empno=7369;UPDATE emp SET sal=sal+50 WHERE empno=7876;COMMIT;执行结果：已更新 1 行。已更新 1 行。已更新 1 行。提交完成。,步骤4：检查LOGSAL表中记录的信息：SELECT * FROM logerr;执行结果为： NUM MESSAGE - -1 雇员SMITH的原工资：800新工资：1300已选择 1 行。说明：本例中，在emp表的sal列上创建了一个由UPDATE事件触发的行级触发器，触发器的名称是LOG_SAL。该触发器由WHEN语句限定，只有当被修改工资的雇员职务为CLERK，且修改的工资超过200时才
21、进行触发，否则不进行触发。,所以在验证过程中，虽然修改了3条记录，但通过查询语句发现：第一条修改语句修改编号为7788的SCOTT记录，因为SCOTT的职务是ANALYST，不符合WHEN条件，没有引起触发器动作；第二条修改语句修改编号为7369的SMITH的记录，职务为CLERK，因为增加的工资(500)超过了200，所以引起触发器动作，并在LOGERR表中进行了记录；第三条修改语句修改编号为7876的雇员ADAMS的记录，虽然ADAMS的职务为CLERK，但修改的工资(50)没有超过200，所以没有引起触发器动作。,注意：在WHEN条件中引用new和old不需要在前面加“: ”。在以上实例
22、中，记录了对工资的修改超出范围的信息，但没有限制对工资的修改。那么当对雇员工资的修改幅度不满足条件时，能否直接限制对工资的修改呢？答案是肯定的。,【训练3】 创建触发器CHECK_SAL，当对职务为CLERK的雇员的工资修改超出500至2000的范围时，进行限制。步骤1：创建和编译以下触发器：CREATE OR REPLACE TRIGGER CHECK_SALBEFOREUPDATEON empFOR EACH ROWBEGIN,IF :new.job=CLERK AND (:new.sal2000) THENRAISE_APPLICATION_ERROR(-20001, 工资修改超出范围,
23、操作取消！);END IF;END;执行结果：触发器已创建。,步骤2：在EMP表中插入记录：UPDATE emp SET sal=800 WHERE empno=7876;UPDATE emp SET sal=450 WHERE empno=7876;COMMIT;执行结果：UPDATE emp SET sal=450 WHERE empno=7876*ERROR 位于第 1 行:ORA-20001: 工资修改超出范围,操作取消！ORA-06512: 在“STUDENT.CHECK_SAL“, line 3ORA-04088: 触发器 STUDENT.CHECK_SAL 执行过程中出错提交完成
24、。,步骤3：检查工资的修改结果：SELECT empno,ename,job,sal FROM emp WHERE empno=7876;执行结果为：EMPNO ENAME JOB SAL - - - -7876 ADAMS CLERK 800,说明：在触发器中，当IF语句的条件满足时，即对职务为CLERK的雇员工资的修改超出指定范围时，用RAISE_APPLICATION_ERROR语句来定义一个临时定义的异常，并立即引发异常。由于触发器是BEFORE类型，因此触发器先执行，触发器因异常而终止，SQL语句的执行就会取消。通过步骤2的执行信息可以看到，第一条语句修改编号为7876的雇员ADAMS的工资为800，成功执行。第二条语句修改雇员ADAMS的工资为450，发生异常，执行失败。这样就阻止了不符合条件的工资的修改。通过步骤3的查询可以看到，雇员ADAMS最后的工资是800，即发生异常之前的修改结果。,【训练4】 创建一个行级触发器CASCADE_UPDATE，当修改部门编号时，EMP表的相关行的部门编号也自动修改。该触发器称为级联修改触发器。步骤1：创建和编译以下触发器： CREATE TRIGGER CASCADE_UPDATE AFTER UPDATE OF deptno ON DEPT FOR EACH ROW BEGIN,
