oracle数据库触发器(trigger)用法总结

时间:2022-11-08 05:07:22

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语句引发一次,不管影响几行。

oracle数据库触发器(trigger)用法总结

图1:trigger的触发顺序

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

1、before、after触发器

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

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859 -- statement级before trigger
CREATE OR REPLACE TRIGGER secure_emp  BEFORE INSERT OR DELETE OR UPDATE ON employeesBEGIN  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 ROWBEGIN  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 ROWBEGIN  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触发器,一般都是用在复杂视图上,用于对复杂视图中相关表的更新操作。如下例所示(代码收录自网络):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748 -- 创建表
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 ROWDECLARE  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

1234 ALTER TRIGGER trigger_name DISABLE; -- 失效,生效改为enableALTER TABLE table_name DISABLE ALL TRIGGERS; -- 批量失效,批量生效改为enableALTER TRIGGER trigger_name COMPILE; -- 重新编译triggerDROP 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。