概述

  • 触发器(trigger)
  1. 类似于事件处理,当数据库某一个“触发事件”发生时,引起数据库的操作。
  2. 用途
  • 执行DML语句,使用 insert、update、delete;
  • 执行DDL(create ,drop,alter)
  • 引发数据库事件发生,(系统启动退出,产生异常,错误)
  • 引发用户事件(登录退出数据库)
  1. 模板
create [or replace] trriger tri_name
[before|after|instead of] tri_event
on table_name|view_name|user_name|db_name
[for each row][when tri_contidion]
begin
plsql_sentences;
end tri_name;

4.分析

  1. before | after | instead of

描述触发时机,before,after分别在dml触发之前、之后。
before 便于防止某些错误操作发生而便于回滚事务或实现某些业务规则。
after 便于记录该操作或做某些事件后处理信息。
instead of 表示为 替代触发器。

  1. on

表示操作的数据表、视图、用户模式(schema)和数据库(database)。

  1. for each row

指定为行级触发器,不指定就是语句触发器(无论什么情况,plsql_sentences都只执行一次)

  1. tri_event

触发事件 如insert 、update、delete、create、alter、drop等。

  1. tri_condition

触发条件表达式 只有该表达式为true时才会触动触发器。

  1. plsql_sentences中的条件谓词

plsql_sentences执行语句块,条件谓词有 inserting 、updating、deleting等,表示在处理语句块中对以上三种操作 的分类执行对应语句。
书写方式 : if inserting then …;elsif updating then …;
当然:条件谓词还可以判断 正在对是否某一个特定列操作。
如: if updating(dname) then

  1. plsql_sentences中的l列标识符

:new (新值标识符) :old(原值标识符)
数据类型(%rowtype)
用来表示当前操作正受到影响的数据行的新值行、原值行。
如:update操作一行数据时,update之前该行数据有原值行。update后就是数据变为新值了,也就是新值行。
而 insert只有新值行,delete只有原值行。

这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;

分类

1.语句级触发器

  • 针对一条DML语句而引起的触发器执行。
  • 没有for each row ,无论操作多少行数据,语句只执行一次。

2.行级触发器

  • 行级触发器会针对DML操作所影响的每一行数据分别执行一次语句块。
  • 需要使用 for each row

3.instead of 触发器

  • 替换触发器,关键字 instead of 。
  • 定义在视图上,由于视图有时是由多个基表连接组成的。这时不能通过视图 对原表进行 DML操作。所以替换视图就是当执行视图的DML操作时,由触发器代替用来完成该操作的。
  • on 关键字 所以是只能为view的。

4.用户事件触发器

  • 是因为进行DDL操作或者用户登录、退出等操作而引起的一种触发器。
  • 引起该类触发事件,常见用户事件包括:

create、alter、drop、analyze、comment、grant、revoke、rename、truncate、suspend、logon、logoff、等

  • 常见事件属性

ora_dict_obj_name 获取DDL操作对应的数据库对象
ora_dict_obj_type 获取DDL操作对应的数据库对象类型(table,view,schema等)
ora_sysevent 获取引起触发器触发的 系统事件名称(类型)
ora_login_user 获取登录用户名

5.系统触发器

由数据库系统启动或者退出、产生异常错误等事件而触发的触发器。

6.编写注意事项

  • 触发器不接受参数。

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

  • 在一个表上的触发器越多,对在该表上的DML操作的性能影响就越大。

  • 触发器最大为32KB。若确实需要,可以先建立过程,然后在触发器中用CALL语句进行调用。

  • 触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE),不能使用DDL语句(CREATE、ALTER、DROP)。

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

  • 在触发器主体中调用的任何过程、函数,都不能使用事务控制语句。

  • 触发器主体中不能申明任何Long和blob变量新值new和旧值old也不能指向表中的任何long和blob列

  • 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。


参考:

  • -例1: 建立一个触发器,当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表emp_his中去。
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 

CREATE OR REPLACE TRIGGER triger1
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;

--测试(注意观察错误信息与查询结果):
DELETE emp WHERE empno=7788;
SELECT * FROM emp_his;

SELECT * FROM emp_his;
--删除:
DROP TRIGGERtrigger1;


  • -例2:限制对dept表修改(包括INSERT,DELETE,UPDATE)的时间范围,即只允许在非工作时间修
    改dept表。
CREATE OR REPLACE TRIGGER triger2
BEFORE INSERT OR DELETE OR UPDATE
ON dept
BEGIN
IF (TO_CHAR(sysdate,'DAY') NOT IN ('星期六', '星期日')) AND (TO_CHAR(sysdate, 'HH24:MI') BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20001, '上班时间,不能修改dept表');
END IF;
END;
--测试(注意观察错误信息与查询结果):
UPDATE dept SET loc = 'Shanghai' WHERE deptno = 20;

DELETE FROM dept WHERE deptno = 20;
INSERT INTO deptVALUES (60, 'Development',, 'Shanghai');
ALTERTRIGGERtrigger2DISABLE;
INSERT INTO deptVALUES (60, 'Development',, 'Shanghai');
ROLLBACK;

  • -例3:限定只对部门号为30的记录进行行触发器操作。
CREATE OR REPLACE TRIGGER trigger3
BEFORE UPDATE OF sal, comm OR DELETE
ON emp
FOR EACH ROW
WHEN (old.deptno = 30)
BEGIN
CASE
WHEN UPDATING ('sal') THEN
IF :new.sal < :old.sal THEN
RAISE_APPLICATION_ERROR(-20001, '部门30的人员的工资不能降');
END IF;
WHEN UPDATING ('comm') THEN
IF :new.comm < :old.comm THEN
RAISE_APPLICATION_ERROR(-20002, '部门30的人员的奖金不能降');
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, '不能删除部门30的人员记录');
END CASE;
END;
--测试(注意观察错误信息与查询结果):
UPDATE emp SET sal = 8000 WHERE empno = 7499;

DELETE FROM emp WHERE emp in (7499,7369);
ALTER TRIGGER trigger3 DISABLE;
DELETE FROM emp WHERE emp in (7499,7369);
ROLLBACK;
  • -例4:利用行触发器实现级联更新。在修改了主表dept中的deptno之后(AFTER),级联的、自动的更新子表emp表中原来在该部门的员工的deptno。
CREATE OR REPLACE TRIGGER trigger4
AFTER update OF deptno
ON dept
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('旧的deptno值是'||:old.deptno||'、新的deptno值是'||:new.deptno);
UPDATE emp SET deptno = :new.deptno
WHERE deptno= :old.deptno;
END;
--测试(注意观察错误信息与查询结果):
UPDATE dept SET loc = 'Shanghai' WHERE deptno = 20;

DELETE FROM dept WHERE deptno = 20;
UPDATE dept SET deptno = 70WHERE deptno = 30;
SELECT * FROM dept ;
SELECT * FROM emp WHERE deptno = 30;
SELECT * FROM emp WHERE deptno = 70;
ROLLBACK;
ALTERTRIGGERtrigger4DISABLE;
UPDATE dept SET deptno = 70WHERE deptno = 30;
SELECT * FROM dept ;
SELECT * FROM emp WHERE deptno = 30;
SELECT * FROM emp WHERE deptno = 70;
ROLLBACK;

  • 例5:Instead of 触发器。

--新建视图:
CREATE OR REPLACE VIEW emp_view AS
SELECT deptno, count(*) total_employeer, sum(sal) total_salary
FROM emp GROUP BY deptno;
--测试(注意观察错误信息):
DELETE FROM emp_view WHERE deptno=10;

--创建Instead of 触发器(instead of 与行级触发器)
CREATE OR REPLACE TRIGGER trigger5
INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno= :old.deptno;
END ;

--测试(注意观察错误信息与查询结果):
DELETE FROM emp_view WHERE deptno=10;

SELECT * FROM emp WHERE deptno = 10;
ROLLBACK;

例6:系统触发器。
将操作CREATE、DROP存储在log_info表

--创建表
CREATE TABLE log_info(
op_user VARCHAR2(15),
op_date VARCHAR2(19),
opr_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15));
--创建触发器
CREATE OR REPLACE TRIGGER trigger6
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
--测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;
查看效果
SELECT * FROM log_info;