2,668
社区成员
发帖
与我相关
我的任务
分享
当两张表相互协调数据时,表1更新表2的数据,而表2又更新表1的数据,这样就会循环触发而报错.
解决办法,把表换成视图,在视图上建立触发器更新表
--创建表
CREATE TABLE TEST1 ( SID VARCHAR2(10), SNAME VARCHAR2(60) );
--创建表二
CREATE TABLE TEST2 ( SID VARCHAR2(10), SNAME VARCHAR2(60) );
-------------------------------------------------------------------
--基于表创建视图
CREATE OR REPLACE VIEW V_TEST1 AS SELECT * FROM TEST1;
CREATE OR REPLACE VIEW V_TEST2 AS SELECT * FROM TEST2;
-------------------------------------------------------------------
SELECT * FROM TEST1;
SELECT * FROM TEST2;
-------------------------------------------------------------------
UPDATE TEST1 SET SNAME = 'this is a good day!' WHERE SID = 'S0001';
UPDATE TEST2 SET SNAME = 'this is a bad day!' WHERE SID = 'S0001';
UPDATE V_TEST1 SET SNAME = 'this is a good day!' WHERE SID = 'S0001';
UPDATE V_TEST2 SET SNAME = 'this is a bad day!' WHERE SID = 'S0001';
-------------------------------------------------------------------
--创建触发器 CREATE OR REPLACE TRIGGER UPDATE_TEST2 INSTEAD OF UPDATE OR INSERT ON V_TEST1
--FOR EACH ROW DECLARE BEGIN UPDATE TEST2 SET SNAME = :NEW.SNAME WHERE SID = :OLD.SID; END;
--注意这里是 INSTEAD OF 而不是 after or befor
-------------------------------------------------------------------
CREATE OR REPLACE TRIGGER UPDATE_TEST1 INSTEAD OF UPDATE OR INSERT ON V_TEST2 FOR EACH ROW DECLARE BEGIN
UPDATE TEST1 SET SNAME = :NEW.SNAME WHERE SID = :OLD.SID; END;