DML触发器(dml trigger)

发表于 更新于 分类于

Oracle DML触发器实现功能：
很多时候我们或者客户有如下需求：
在A.B表上添加触发器.
触发事件：UPDATE,DELETE,INSERT 后触发；对表的每一行触发器执行一次；记录用户名，操作类型，操作时间，修改或删除前的数据，新增的数据。 

1
2
3
4
5
6
7
/*  
①、确认该表没有失效的触发器以及没有已经存在重复的日志表名  
②、创建对应表的触发器日志表，并添加 记录用户名，操作类型，操作时间等列  
③、修改trigger里对日志表插入的内容创建trigger  
④、查看触发器状态，使其enable  
⑤、测试触发器是否可用或者报错  
*/

1、创建日志表并增加审计列

CREATE TABLE TEST.TEST1_LOG  
AS  
   SELECT *  
     FROM TEST.TEST1  
    WHERE 1 = 0;  
  
ALTER TABLE TEST.TEST1_LOG  
   ADD t_operator VARCHAR2 (100);  
  
ALTER TABLE TEST.TEST1_LOG  
   ADD t_action VARCHAR2 (100);  
  
ALTER TABLE TEST.TEST1_LOG  
   ADD t_date date;  
  
ALTER TABLE TEST.TEST1_LOG  
   ADD t_remark VARCHAR2 (100);

2、创建触发器

CREATE OR REPLACE TRIGGER TEST.TRG_TEST1

《《《《《注意这里最好提起查是否有之前的重复trigger名，避免误replace

AFTER INSERT OR DELETE OR UPDATE  
   ON TEST.TEST1  
   FOR EACH ROW  
DECLARE  
   v_havenew   BOOLEAN := FALSE;  
   v_haveold   BOOLEAN := FALSE;  
   v_action    VARCHAR2 (32);  
BEGIN  
   IF INSERTING   
   THEN  
      v_havenew := TRUE;  
      v_action := 'INSERT';  
   END IF;  
  
   IF UPDATING  
   THEN  
      v_havenew := TRUE;  
      v_action := 'UPDATE';  
      v_haveold := TRUE;  
   END IF;  
  
   IF DELETING  
   THEN  
      v_action := 'DELETE';  
      v_haveold := TRUE;  
   END IF;  
  
   IF TRUE = v_haveold  
   THEN  
      INSERT INTO TEST.TEST1_LOG (id,        
      -----/*注意这里insert日志表的列要参照日志的列补充上，我这里的测试表只有id一个列*/  
                                         t_operator,  
                                         t_action,  
                                         t_date,  
                                         t_remark)  
           VALUES (:old.id,  
                   USER,  
                   v_action,  
                   SYSDATE,  
                   'old');  
   END IF;  
  
   IF TRUE = v_havenew  
   THEN  
      INSERT INTO TEST.TEST1_LOG (id,   
      -----/*注意这里insert日志表的列要参照日志的列补充上，我这里的测试表只有id一个列*/  
                                         t_operator,  
                                         t_action,  
                                         t_date,  
                                         t_remark)  
           VALUES ( :new.id,  
                   USER,  
                   v_action,  
                   SYSDATE,  
                   'new');  
   END IF;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      NULL;  
END TRG_TEST1;

3、创建完成后查看trigger状态：

select trigger_name, status from dba_triggers where owner = 'TEST' and trigger_name = 'TRG_TEST1';   
TRIGGER_NA STATUS  
---------- ------------------------  
TRG_TEST1  ENABLED

《《《《《《enable 如果不是enable手工enable如下

ALTER TRIGGER "TEST"."TRG_TEST1" ENABLE;

4、增删改测试

SQL> update TEST.test1 set id=5 where id=2;  
1 row updated.  
SQL> commit;  
Commit complete.  
---------------------------------------------------------  
SQL> delete from TEST.test1 where id=8;  
1 row deleted.  
SQL> commit;  
Commit complete.  
-----------------------------------------------------  
SQL>  insert into TEST.test1 values(10);  
1 row created.  
SQL> commit;  
Commit complete.

5、查看日志表对应是否有记录。

select * from  TEST.TEST1_LOG order by t_date desc;

转自：http://blog.itpub.net/31043804/viewspace-2119546/