时间戳

首页 > 技术文章 > Oracle触发器

Oracle触发器

zbh355376 2021-02-02 17:20 原文

  触发器（trigger）提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行，触发器经常用于加强数据的完整性约束和业务规则等。

  触发器是附加在数据库对象上的一段代码，它指定了触发的时机和事件，如：before/after/instead of和insert，update，delete。一般在操作这些有附加触发器对象时，用触发器记录操作日志，或进行数据备份和数据校验。

  触发器会给数据库带来不稳定，因为它可能在执行时抛出异常而影响正常的数据操作，有时数据库对象上附加的触发器个数多，或流程复杂，势必会影响数据库性能，所以，一般不建议使用，除非特别清楚触发器的风险和隐患。想达到某一种目的，往往有多个替代方案。

  触发器一般建立在表或视图上，也可以建立在用户或数据库层面上。触发器不能主动调用，只有在对相应对象操作时自动触发。

一、 触发器的作用

  1. 可在写入数据表前，强制检验或转换数据。
  2. 触发器发生错误时，异动的结果会被撤销。
  3. 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器
  4. 可依照特定的情况，替换异动的指令（INSTEAD OF）

二、 DML触发器

DML触发器有语句级和行级

语句级

语句级触发器对一个SQL操作只做一次触发，不能访问具体的行、列数据，适合记录操作日志。

  • 创建操作日志表：
create table T_LOGS
(
  id        NUMBER,-- 主键
  op_time   DATE, -- 操作时间
  op_user   VARCHAR2(20), -- 操作用户
  op_event  CHAR(6), -- 操作事件类型insert/update/delete
  op_target VARCHAR2(50)-- 操作的目标对象
);
  • 创建语句级触发器：
-- 关键字trigger
create or replace trigger trg_emp1
  before insert or update or DELETE -- 时机和事件,事件可以是它们的组合
  on emp -- 作用的对象
declare
  v_use VARCHAR2(20);-- 操作用户
  v_event CHAR(6);-- 操作事件
BEGIN
  -- INSERTING  UPDATING DELETING USER这些都是Oracle的常量，可以直接使用
  IF INSERTING THEN
    v_event:='增加';
  ELSIF UPDATING THEN
    v_event:='修改';
  ELSIF DELETING THEN
    v_event:='删除';
  END IF;      
  INSERT INTO t_logs(id,op_time,op_user,op_event,op_target)
  VALUES(seq_id.nextval,SYSDATE,USER,v_event,'emp');
end trg_emp1;

行级

给dept表建行级触发器，操作dept表的同时更新它的副表dept2，增删改时两张表数据同步：

CREATE OR REPLACE TRIGGER trg_dept 
 BEFORE INSERT OR UPDATE OR DELETE -- 时机和事件
 ON dept -- 作用的对象
 FOR EACH ROW -- 代表行级
DECLARE
BEGIN
  -- :new代表了新加入emp表中的数据行，可以得到每一列的值
  -- ：old代表原来的行，在进行修改和删除时需要通过:old获取原来的数据
  IF inserting THEN
    INSERT INTO dept2 VALUES(:new.deptno,:new.dname,:new.loc);
  ELSIF updating THEN
    UPDATE dept2 SET deptno=:new.deptno,dname=:new.dname,loc=:new.loc WHERE deptno=:old.deptno;
  ELSE
    DELETE FROM dept2 WHERE deptno=:old.deptno;
  END IF;
END;

三、 DDL触发器

在scoot用户下操作数据库对象时可以记录操作日志：

CREATE OR REPLACE TRIGGER trg_scott1
AFTER DDL ON scott.schema -- 对scott用户进行DDL的监控
BEGIN
  /*ora_dict_obj_nameDDL操作的对象名
  ora_dict_obj_typeDDL操作的对象类型table、view、db_name*/
  INSERT INTO t_logs(ID,op_time,op_user,op_event,op_target)
  VALUES(seq_id.nextval,SYSDATE,'scott',ora_sysevent
  ,ora_dict_obj_name||'-'||ora_dict_obj_type);
END;

注：建立了触发器后，不能再对t_logs表进行修改和删除，因为这也是DDL操作，而这个表正是记录这些操作的日志表，会报错。

四、 数据库级触发器

以DBA角色登录数据库，创建触发器，记录用户登录的日志：

-- 因为是DBA角色登录，所以代码中的数据对象要有用户schema
--scott就是scott的用户schema
CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE -- 对数据库登录监控  BEFORE LOGOFF可以在用户登出时记录日志
BEGIN
  -- sys_context('USERENV', 'SESSIONID')是获取用户的会话标识
  INSERT INTO scott.t_logs(ID,op_time,op_user,op_event,op_target)
  VALUES(scott.seq_id.nextval,SYSDATE,user,'LOGIN'
  ,sys_context('USERENV', 'SESSIONID'));
END;

五、 替代触发器

替代视图完成表数据的维护，instead of,before,after是三选一，所以替代触发器是没有before和after选项。

create or replace trigger trg_myvw
  instead of insert or update or delete
  on my_vw
  for each row
declare
  -- local variables here
begin
  IF INSERTING THEN
    INSERT INTO emp(empno) VALUES(:new.empno);
  ELSIF UPDATING THEN
    -- :new是新数据行
    UPDATE emp SET ename=:new.ename WHERE empno=:new.empno;
  ELSE
    -- 对于没有更新操作来说，:old就是原数据行
    -- 需要通过:old来获取数据，:new在此处不适合
    DELETE emp WHERE empno=:old.empno;
  END IF;
end trg_myvw;

推荐阅读