触发器学习与整理

1.基本准则

创建trigger时，遵循下列准则

A.触发器的名称,CREATE TRIGGER语句文本的字符长度不能超过32KB

B.主题表的名称

C.触发器激活的时间(在修改操作执行前后执行后)

D.触发器事件(insert、update、delete)

E.旧转换变量值(如果存在)

F.新转换变量值(如果存在)

G.旧转换表值(如果存在)

H.新转换表值(如果存在)

I.粒度(fow each row或for each statement)

J.触发器的触发动作(包括触发条件和触发语句)

K.触发事件是update时，如果仅当update语句中指定了特定列时才应触发触发器，那么就必须声明触发器列列表

L.触发器不接受参数

M. 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

N.在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大

O.触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE）

P.触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了

Q.在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

R.执行顺序：如果在一个表上定义多个触发器（也就是说，两个 BEFORE INSERT 触发器被定义），他们将按照被创建的次序来被执行。当然，BEFORE 触发器总是在 AFTER 触发器之前被激活而不必考虑其创建次序。表的其他约束条件（如主/外键约束，唯一性约束以及检查约束）同样会在 BEFORE 触发器之后，AFTER 触发器之前被检查

 

 

2. 触发器触发次序

1.       执行 BEFORE语句级触发器;

2.       对与受语句影响的每一行：

l         执行 BEFORE行级触发器

l         执行 DML语句

l         执行 AFTER行级触发器 

3.       执行 AFTER语句级触发器

 

 

3.语法详解

NO CASCADE BEFORE INSERT ON :意味着触发的操作是在数据实际插入表之前发生的，并且触发器的操作将不会导致激活任何其他触发器。对于所有的 BEFORE触发器，要加上关键字NO CASCAD,只有后触发器AFTER和INSTEAD OF触发器中才可以包含下列一个或多个SQL语句

 

begin atomic 。。。End: BEGIN ATOMIC 规定了触发器里的操作要么不执行，要么就要全部执行。如果在触发器操作执行过程中发生了错误，所有操作都将回退以维护数据的完整性。 如果在 BEGIN ATOMIC 和 END 关键字之间输入多个 SQL 语句，则必须以分号（;）或惊叹号（!）结束每个语句.如果 SQL 包含单个语句，则不需要 BEGIN ATOMIC 和 END 关键字

 

MODE DB2SQL:指定的语句

 

REFERENCING: 子句说明相关名称,REFERENCING NEW AS N 相当于指定新影响的行或结果的别名为N，以便在执行语句中进行引用

 

INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。

 

UPDATING: [（column_1,column_2,…,column_x）]：当触发事件是UPDATE      时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。

 

DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE

 

FOR EACH ROW :指触发器在操作每一行时被激活，FOR EACH STATEMENT是指在执行每条SQL语句时被激活；

因此如果有一条SQL语句要修改10跳记录，则FOR EACH ROW要运行10此，而FOR EACH STATEMENT只运行一次

 

IF INSERTING THEN：条件判断语句，当执行插入语句时，执行then后的语句

 ::NEW 修饰符访问操作完成后列的值

4.触发器例子

、创建触发器

--插入时触动

CREATE TRIGGER administrator.tri_insert 

AFTER INSERT ON administrator.A

REFERENCING NEW AS N 

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID,NAME) VALUES(N.ID,N.NAME );

END   

--测试: INSERT INTO  administrator.A VALUES ('3','Name');

 

oracle例子：CREATE OR REPLACE TRIGGER tr_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 TRIGGER administrator.tri_delete

AFTER DELETE ON administrator.A

REFERENCING OLD AS O 

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID) VALUES(O.ID);

END --测试:DELETE FROM administrator.A WHERE ID = '3' 

 

--更新时触动 

CREATE OR REPLACE  TRIGGER administrator.tri_update

AFTER UPDATE OF NAME ON administrator.A

REFERENCING NEW AS N OLD AS O 

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID,NAME) VALUES(O.ID,N.NAME);

END  

--测试:UPDATE administrator.A SET NAME = 'n2_name'  

 

--有资料提示 DB2 9.7以上版本支持 AFTER INSERT OR DELETE OR UPDATE 写法，可是9.7版本并不支持

例子：

  CREATE OR REPLACE TRIGGER HIRED
   AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
   REFERENCING NEW AS N OLD AS O FOR EACH ROW
   BEGIN
      IF INSERTING THEN 

UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;

      ELSEIF 

DELETING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;

      ELSEIF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
         THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
      END IF;
   END;