我平时比较少用触发器,主要是因为程序逻辑不对的时候不容易发现错误,有时数据量大了也可能
产生性能上的问题,但这个东西总有用武之地,在很多场合还是会起到巨大的作用。
这两天就遇到一个问题,有两张表的一个字段需要进行同步更新,也就是A表修改时要把对应的B表的记录
字段修改,反过来B表修改时也要把A表的修改,保持两边数据的一个同步,这个可以在前台很容易的实现,但开发
人员不想修改代码了,就考虑在后台用trigger实现。
功能很简单,但在实现时遇到一个问题,就是A上的DML触发了上面的TRIGGER,然后这个TRIGGER去更新B表,这样
就会触发B表上的触发器,而B表上的TRIGGER又会更新A表,这样就迭代触发,没有结束了,也就是会产生变异表(mutating)
我不知道ORACLE的触发器是否有属性来限制这种情况的发生,但以前做SQL SERVER时知道有种Instaed of的触发器,他表示
当DML启动他后,他将以TRIGGER里的代码来代替这个DML动作,也就是DML不会真正的执行,只会启动INSTEAD TRIGGER,最终
执行的是TRIGGER里面的编码。
查看了Docs,看到ORACLE也支持这个类型的触发器,但这个只能建立到视图上,不能基于表建立,我要的功能肯定是可以
实现的,在这里我把原表进行了rename,引如了两张视图,名字就是以前的表名,这样对于他们前台应用就做了个
透明的切换,然后在两个视图上建立INSTEAD触发器,将任何两个视图上的更新都传播到后面的两个基表,这样不管你更新那个
视图,我都可以捕获到数据,以代码在后面更新,也不存在互相触发,因为触发器修改的对象已经转移到表了,而此时表上是没有
trigger的,呵呵!!!
过程如下
--创建测试表
SQL> create table mytest1(row_num number,row_name varchar2(50));
表被创建
SQL> create table mytest2(row_num number,row_name varchar2(50));
表被创建
--测试数据
SQL> INSERT INTO MYTEST1 VALUES(1,'Fuck!!!');
1 行 已插入
SQL> INSERT INTO MYTEST2 VALUES(1,'Watch your mouth!!!');
1 行 已插入
SQL> COMMIT;
提交完成
--先在一个表上创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 BEFORE UPDATE
3 ON MYTEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /
触发器被创建
--测试更新
SQL> set serveroutput on
SQL> UPDATE MYTEST1 SET ROW_NAME = 'DO it!!!';
DO it!!! Fuck!!!
1 行 已更新
--更新成功
SQL> SELECT * FROM MYTEST2;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 DO it!!!
--另外张表创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 BEFORE UPDATE
3 ON MYTEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST1
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /
--产生了变异表,更新失败
SQL> update mytest1 set row_name = 'mouthkkkkkoo';
update mytest1 set row_name = 'mouthkkkkkoo'
ORA-04091: table MYTEST1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
--更新失败
SQL> update mytest2 set row_name = 'mouthkkkkkoo';
update mytest2 set row_name = 'mouthkkkkkoo'
ORA-04091: table MYTEST2 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
--删除触发器
SQL> drop trigger TRI_TEST2;
触发器被删掉
SQL> drop trigger TRI_TEST1;
触发器被删掉
--创建视图
SQL> CREATE VIEW V_TEST1 AS SELECT * FROM MYTEST1;
视图被创建
SQL> CREATE VIEW V_TEST2 AS SELECT * FROM MYTEST2;
视图被创建
--基于视图创建Instead触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 INSTEAD OF UPDATE
3 ON V_TEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /
触发器被创建
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 INSTEAD OF UPDATE
3 ON V_TEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /
触发器被创建
--功能已经实现
SQL> update v_test1 set row_name = 'I rock with you!!!';
1 行 已更新
SQL> commit;
提交完成
SQL> select * from v_test2;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 I rock with you!!!
SQL> update v_test2 set row_name = 'Don''t kick me!!!';
1 行 已更新
SQL> commit;
提交完成
SQL> select * from v_test1;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 Don't kick me!!!
SQL>