oracle数据库触发器（trigger）⽤法总结
触发器的意思就是当我们对数据库对象（⼀般是表或视图）进⾏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,
:ployee_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
:NEWmission_pct := 0;
ELSIF :OLDmission_pct IS NULL THEN
:
NEWmission_pct := 0;
ELSE
:NEWmission_pct := :OLDmission_pct + 0.05;
END IF;
END;
对于:OLD和:NEW的⽤法，需要注意的是它们什么时候存在什么时候可⽤，:old表⽰引⽤以前的列值，:new表⽰引⽤最新的列值，这两个变量都只能在for each row存在的时候才可⽤，且update语句:old和:new都有，delete语句只有:old，insert语句只有:new。
2、instead of触发器
sql中update什么意思对于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) REF
ERENCES 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 4ALTER 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。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。