Oracle触发器

触发器:
类似于函数和存储过程,都是具有声明部分,执行部分和异常处理部分的命名的pl/sql
触发器必须在数据库中以独立的对象的身份存储,并且不能与包和块具有本地关系
触发器是在时间发生时隐式运动的,并且触发器不能接收参数
运行级触发器的方式叫做激发Firing触发器
触发事件可以是DML/DDL/启动/关闭等操作

一:触发器的作用

1.1安全性

可以基于数据库的值,使用户具有操作数据库的某种权利
可以基于时间限制
可以基于数据库中的数据限制用户操作

1.2审计

可以跟踪用户对数据库的操作
审计用户操作数据库的语句
把用户对数据库的更新写入审计表

1.3实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束

1.4实现复杂的非标准的数据库相关完整性规则

触发器在进行连环更新方面的作用包括:
在修改或删除时级联修改或删除其他表中与之匹配的行
在修改或删除时把其他表中与之匹配的行设成null值
在修改或删除时把其他表中与之匹配的行级联设成默认值

触发器能够拒绝或回滚那些破坏相关完整性的变化
触发器还可以同步实时的复制表的数据

二:触发器的种类

DML触发器
替代触发器
事件触发器

2.1DML触发器

DML触发器是定义在表上的触发器,由DML事件触发
insert ->
update ->
delete ->

编写DML触发器的要素:
确定触发的表,即在其上定义触发器的表
确定触发的事件.
确定触发的时间,有before / after
确定触发器的级别, 有语句级触发器和行级触发器,语句级触发器只触发一次,行级触发器会触发多次
语句级触发器是在表上或者某些情况下的视图上的执行的特定语句或者语句组上的触发器
在语句触发器的基础上如果指定FOR EACH ROW就变成行级触发器了,在行级触发器上sql语句影响的每行都会触发一次触发器.

2.2替代触发器

INSTEAD-OF 替代触发器只能定义在视图上(可以是关系或对象),
与DML触发器不同的是,DML触发器是在DML操作之外的,而替代触发器则代替激发它的DML语句运行
替代触发器是行一级的,
用法: 替代直接对视图的 insert/update/delete操作

2.3事件触发器

事件触发器是指基于oracle系统事件或者客户事件所建立的触发器.通过触发器可以跟踪系统或数据库的变化的机制
事件触发器可细分为DDL事件触发器和数据库事件触发器
DDL事件触发器包括:
CREATE->
ALTER->
DROP->
数据库事件触发器包括:
服务器启动 STARTUP: 数据打开时触发
服务器关闭 SHUTDOWN: 在使用normal/immeiate选项关闭数据库时触发
用户登陆 LOGON: 当用户连接到数据库并建立会话时触发
用户注销 LOGOFF: 当一个会话从数据库中断开时触发
服务器错误 SERVERERROR: 发生服务器错误时触发

三:DML触发器

触发器定义项:
触发器名称
触发语句
触发器限制
触发操作

触发器可以包括任何合法的pl/sql语句,但是除了一下情况:
触发器不能执行commit , rollback , savepoint 语句而且不可以调用执行这些语句之一的函数或过程
触发器不可以声明 long , long raw 类型变量
触发器不可以在定义它的表上执行DML操作

注意:
触发器可以使用show errors 检查编译错误.
创建触发器需要create trigger系统权限

3.1基本语法

CREATE [OR REPLACE] TRIGGER 触发器名称
触发时间 {BEFORE|AFTER|INSTEAD OF} 触发事件1 [触发事件2…]
ON 表名
WHEN 触发条件
[FOR EACH ROW]
DECLARE
声明部分
BEGIN
主体部分
END;

解释:
触发时间: before / after
触发事件: insert / update / delete
事件可以出现并行,中间使用 OR 连接

DML触发器注意问题:
相同时间/相同事件触发的触发器只有最后定义的那个有效,其余的无效
一个触发器可以由多个不同DML操作触发,在触发器中可用inserting / updating / deleting 谓词来区别不同的DML操作,这些谓词在IF分支条件语句中作为判断条件来使用
在行级触发器中,用:new 和 :old (称为伪记录)来访问数据变更前后的值, insert语句没有:old记录,delete没有:new记录
触发器体内禁止使用commit / rollback / savepoint 语句,也禁止直接或者间接调用含有这些语句的存储过程
执行顺序:
before : 行级 > before :语句级 > after : 行级 > after : 语句级

3.2行级触发器

带 for each row
创建DML触发器

CREATE OR REPLACE TRIGGER my_tri    --指定触发器名称 my_tri
BEFORE     --在执行DML操作之前触发

UPDATE OR INSERT OR DELETE    --触发事件有 update / insert / delete 都可以触发执行
ON tree       --指定被操作表tree就触发该触发器
FOR EACH ROW  --行级触发器标识
  BEGIN  
    IF inserting THEN   --如果是insert操作就执行以下操作

      INSERT INTO t1 VALUES(tmp_seq.nextval,'insert',1);

      ELSIF deleting THEN    --如果是delete操作就触发执行以下操作
        INSERT INTO t1 VALUES(tmp_seq.nextval,'delete',1);

        ELSE   --如果都不是insert/delete 那么就是update操作
          INSERT INTO t1 VALUES(tmp_seq.nextval,'update',1);

          END IF;
          END;

3.2.1使用带条件语句修改表数据触发DML触发器

触发器执行前:

INSERT INTO tree VALUES(13,'E',1);
 DELETE FROM tree WHERE ID = 13

触发触发器执行:
结果:

3.2.2使用不带条件语句的update语句触发DML触发器

UPDATE tree SET ID = 13 WHERE pid = 7;

因为该语句触发修改了多条记录:
结果:

3.3限定条件的行级触发器

CREATE OR REPLACE TRIGGER my_tri2
BEFORE
UPDATE OF ID
ON tree
FOR EACH ROW
WHEN (old.ID >= 6)   --限定条件
    DECLARE

      tmp_num NUMBER;
      BEGIN

        SELECT COUNT(*) INTO tmp_num FROM t1;
        INSERT INTO t1 VALUES(tmp_num,'when',tmp_num-1);
        END;

注意:
1.只有修改表tree 的id的旧值>=6才会触发该触发器,否则不会触发
2.在when() 条件里面old/new关键字不需要在前面加 : 冒号

3.4级联修改触发器

作用于主外键的多表之间的同步更新,成为级联更新.

CREATE OR REPLACE TRIGGER my_tri3
AFTER
UPDATE OF ID
ON t1
FOR EACH ROW
  BEGIN

    UPDATE t2 SET t2.ID = :new.id    --当修改了t1的id,就会触发修改t2的id , 这里的 :new / :old 代表t1
    WHERE t2.id = :old.id;          --注意:这里不能再update t1表的id,否则死循环
    END;

注意:不要在触发器上更新触发器中的 update of 列1… 指定的列,否则又会触发触发器发生死循环

3.5语句级触发器(不需要 for each row)

语句级触发器无论操作语句操作多少行都只触发一次触发器;

3.6利用触发器进行数据同步

SELECT * FROM t2
SELECT * FROM t3   --先创建一样的两个表t2和t3


CREATE OR REPLACE TRIGGER my_tri4
AFTER
INSERT OR UPDATE OR DELETE
ON t2
FOR EACH ROW
  BEGIN

    IF inserting THEN
      INSERT INTO t3 VALUES(:new.id,:new.name,:new.status);

      ELSIF deleting THEN
        DELETE FROM t3 WHERE t3.id = :old.id;

        ELSE
          UPDATE t3 SET t3.name = :new.name , t3.status = :new.status
          WHERE t3.id = :old.id;

          END IF;
          END;

四:替代触发器–只能用于视图

创建替代触发器类似于DML触发器
关键字 : INSTEAD OF

CREATE OR REPLACE TRIGGER my_tri5
INSTEAD OF  --替代触发器
INSERT

ON my_view
BEGIN

  INSERT INTO t2 VALUES(5,'n',1);

  END;

五:事件触发器

5.1数据库事件触发器

数据库事件:
服务器启动:STARTUP
服务器关闭:SHUTDOWN
用户登陆:LOGON
用户注销:LOGOFF
服务器错误:SERVERERROR

CREATE OR REPLACE TRIGGER my_tri6
AFTER
startup     --表示在服务器开启的时候触发执行
ON DATABASE --表示是数据库事件触发器
BEGIN
  DELETE FROM t1;
  END;

5.2DDL事件触发器

DDL事件(create/drop/alter)

CREATE OR REPLACE TRIGGER my_tri6
BEFORE
DROP       --表示在删除表的时候触发执行
ON SCHEMA  --表示是DDL事件触发器
BEGIN
  DELETE FROM t1;
  END;

5.3事件属性函数

太多

六:管理触发器

6.1查看触发器

系统数据字典:
DBA_TRIGGERS
ALL_TRIGGERS
USER_TRIGGERS

select trigger_name,trigger_type , triggering_event from user_triggers ;

6.2禁用/启用触发器

6.2.1 禁用: alter trigger 触发器名称 disable;

6.2.2 启用: alter trigger 触发器名称 enable;

6.3删除触发器

drop trigger 触发器名称;