DML触发器实现功能:
很多时候我们或者客户有如下需求:
在A.B表上添加触发器.
触发事件:UPDATE,DELETE,INSERT 后触发;对表的每一行触发器执行一次;记录用户名,操作类型,操作时间,修改或删除前的数据,新增的数据。
/*
①、确认该表没有失效的触发器以及没有已经存在重复的日志表名
②、创建对应表的触发器日志表,并添加 记录用户名,操作类型,操作时间等列
③、修改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;
很多时候我们或者客户有如下需求:
在A.B表上添加触发器.
触发事件:UPDATE,DELETE,INSERT 后触发;对表的每一行触发器执行一次;记录用户名,操作类型,操作时间,修改或删除前的数据,新增的数据。
/*
①、确认该表没有失效的触发器以及没有已经存在重复的日志表名
②、创建对应表的触发器日志表,并添加 记录用户名,操作类型,操作时间等列
③、修改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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31043804/viewspace-2119546/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31043804/viewspace-2119546/