当两张表相互协调数据时,表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 --注意这里是 INSTEAD OF 而不是 after or befor FOR EACH ROW DECLARE BEGIN UPDATE TEST2 SET SNAME = :NEW.SNAME WHERE SID = :OLD.SID; END; ------------------------------------------------------------------- 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; ------------------------------------------------------------------- drop trigger UPDATE_TEST2; drop trigger UPDATE_TEST1; ------------------------------------------------------------------- it's so easy ,yea!