oracle数据库触发器（trigger）用法总结

http://yedward.net/?id=116

 

触发器的意思就是当我们对数据库对象（一般是表或视图）进行insert、update、delete操作的时候，这些操作会相应的触发一些事件的执行，通常要执行的事件被写成PL/SQL程序，那么这些数据库对象上的事件相关的程序就是触发器（trigger）。oracle数据库中，触发器分为before、after、instead of三种，其中before、after主要对于表操作，instead of主要对于视图操作，因为如果视图是多表的时候，不能直接进行DML操作，这个时候可以建立代替触发器（instead of）来替换事件本身的动作。同时，触发器也可以分为row级和statement级两种，row级的触发器在每次DML一行时执行，statement触发器一个SQL语句引发一次，不管影响几行。

图1：trigger的触发顺序

上图1非常重要，对于trigger的触发顺序的解释一目了然，尤其是下半副图，update数据的时候，对于statement trigger，不管是before还是after，都只会执行一次，而row trigger，却会在每次的udpate的前后执行一次。

1、before、after触发器

创建statement trigger和row trigger的语法直接看下面代码：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

-- statement级before trigger CREATE  OR  REPLACE  TRIGGER  secure_emp    BEFORE  INSERT  OR  DELETE  OR  UPDATE  ON  employees BEGIN    IF (TO_CHAR(SYSDATE,  'DY' )  IN  ( 'SAT' ,  'SUN' ))  OR       (TO_CHAR(SYSDATE,  'HH24:MI' )  NOT  BETWEEN  '08:00'  AND  '18:00' )  THEN      RAISE_APPLICATION_ERROR(-20500,                              'You may modify EMPLOYEES table only on weekdays.' );    END  IF; END ; -- row级before trigger CREATE  OR  REPLACE  TRIGGER  restrict_salary    BEFORE  INSERT  OR  UPDATE  OF  salary  ON  employees    FOR  EACH ROW BEGIN    IF  NOT  (:NEW.job_id  IN  ( 'AD_PRES' ,  'AD_VP' ))  AND  :NEW.salary > 15000  THEN      RAISE_APPLICATION_ERROR(-20202,  'Error' );    END  IF; END ; -- row级after trigger，注意:new和:old的用法 CREATE  OR  REPLACE  TRIGGER  audit_emp_values    AFTER  DELETE  OR  INSERT  OR  UPDATE  ON  employees    FOR  EACH ROW BEGIN    INSERT  INTO  audit_emp_table      (user_name,       TIMESTAMP ,       id,       old_last_name,       new_last_name,       old_title,       new_title,       old_salary,       new_salary)    VALUES      ( USER ,       SYSDATE,       :OLD.employee_id,       :OLD.last_name,       :NEW.last_name,       :OLD.job_id,       :NEW.job_id,       :OLD.salary,       :NEW.salary); END ; -- row级before trigger，注意when的用法 CREATE  OR  REPLACE  TRIGGER  derive_commission_pct    BEFORE  INSERT  OR  UPDATE  OF  salary  ON  employees    FOR  EACH ROW    WHEN  (NEW.job_id =  'SA_REP' ) BEGIN    IF INSERTING  THEN      :NEW.commission_pct := 0;    ELSIF :OLD.commission_pct  IS  NULL  THEN      :NEW.commission_pct := 0;    ELSE      :NEW.commission_pct := :OLD.commission_pct + 0.05;    END  IF; END ;

对于:OLD和:NEW的用法，需要注意的是它们什么时候存在什么时候可用，:old表示引用以前的列值，:new表示引用最新的列值，这两个变量都只能在for each row存在的时候才可用，且update语句:old和:new都有，delete语句只有:old，insert语句只有:new。

2、instead of触发器

对于instead of触发器，一般都是用在复杂视图上，用于对复杂视图中相关表的更新操作。如下例所示（代码收录自网络）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

-- 创建表 CREATE  TABLE  STUDENT (     CODE  VARCHAR2(5),     LNAME VARCHAR2(200) ) CREATE  TABLE  COURSE (      CODE  VARCHAR2(5),      CNAME VARCHAR2(30) ) CREATE  TABLE  ST_CR (     STUDENT VARCHAR2(5),     COURSE  VARCHAR2(5),     GRADE   NUMBER ) -- 表的约束 ALTER  TABLE  STUDENT  ADD  CONSTRAINT  STUDENT$PK  PRIMARY  KEY (CODE); ALTER  TABLE  COURSE  ADD  CONSTRAINT  COURSE$PK  PRIMARY  KEY (CODE); ALTER  TABLE  ST_CR  ADD  CONSTRAINT  ST_CR$PK  PRIMARY  KEY (STUDENT, COURSE); ALTER  TABLE  ST_CR  ADD  CONSTRAINT  ST_CR$FK$STUDENT  FOREIGN  KEY (STUDENT)  REFERENCES  STUDENT(CODE); ALTER  TABLE  ST_CR  ADD  CONSTRAINT  ST_CR$FK$COURSE  FOREIGN  KEY (COURSE)  REFERENCES  COURSE(CODE); -- 创建基于这三个表的视图 CREATE  OR  REPLACE  VIEW  STUDENT_STATUS  AS     SELECT  S.CODE S_CODE, S.LNAME STUDENT, C.CODE C_CODE, C.CNAME COURSE, SC.GRADE GRADE        FROM  STUDENT S, COURSE C, ST_CR SC          WHERE  S.CODE = SC.STUDENT            AND  C.CODE = SC.COURSE -- 创建基于视图的instead of触发器 CREATE  OR  REPLACE  TRIGGER  TRI_STCR  INSTEAD  OF  INSERT  ON  STUDENT_STATUS     FOR  EACH ROW DECLARE    W_ACTION VARCHAR2(1); BEGIN    IF    INSERTING  THEN      W_ACTION :=  'I' ;    ELSE      RAISE PROGRAM_ERROR;    END  IF;      INSERT  INTO  STUDENT(CODE, LNAME)  VALUES (:NEW.S_CODE,:NEW.STUDENT);      INSERT  INTO  COURSE(CODE, CNAME)  VALUES (:NEW.C_CODE, :NEW.COURSE);      INSERT  INTO  ST_CR(STUDENT, COURSE, GRADE)         VALUES (:NEW.S_CODE, :NEW.C_CODE, :NEW.GRADE); END ; -- 对视图执行数据插入，如果这里没有建过instead of触发器，很明显对复杂视图进行DML操作是会出错的 INSERT  INTO  STUDENT_STATUS(S_CODE, STUDENT, C_CODE, COURSE, GRADE)         VALUES ( '001' , 'Mike' , 'EN' , 'English' ,86);

3、管理Trigger

1 2 3 4

ALTER  TRIGGER  trigger_name DISABLE;  -- 失效，生效改为enable ALTER  TABLE  table_name DISABLE  ALL  TRIGGERS;  -- 批量失效，批量生效改为enable ALTER  TRIGGER  trigger_name COMPILE;  -- 重新编译trigger DROP  TRIGGER  trigger_name;  -- 删除trigger

oracle数据库中trigger的使用，大概就是这样，虽然看着比较多，但是在实际中确是不太建议使用的，因为维护起来很不方便，所以在能不用trigger的地方还是不用为好。

2015年01月16日补充：

oracle对触发器提供了如下的一些谓词：

（1）inserting：当触发事件是insert时，取值为true，否则为false。

（2）updating[(column_1, column_2, ..., column_x)]：当触发事件是update时，如果修改了column_x列，则取值为true，否则为false。其中，column_x是可选的。

（3）deleting：当触发事件是delete时，则取值为true，否则为false。