Oracle触发器

  触发器(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;
posted @ 2021-02-02 17:20  逍遥客灬  阅读(553)  评论(0编辑  收藏  举报