有三个表TA TB TC
其中TA TB分别对应有两个触发器 trA trB
update TA时,触发了trA,其中有一句
update TB set tbID = 1;
此句触发了trB
update TC set tcID = 1 where tcID = (Select taID from TA where taID > 10 and rownum = 1);此句查询TA引发异常
请问不改SQL语句的情况下 怎么能解决。
请各位指点,不胜感激!
其中TA TB分别对应有两个触发器 trA trB
update TA时,触发了trA,其中有一句
update TB set tbID = 1;
此句触发了trB
update TC set tcID = 1 where tcID = (Select taID from TA where taID > 10 and rownum = 1);此句查询TA引发异常
请问不改SQL语句的情况下 怎么能解决。
请各位指点,不胜感激!
[TEST@ora10gr1#2009-11-04/08:09:18] SQL>create table ta(taid int);Table created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>create table tb(tbid int);Table created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>create table tc(tcid int);Table created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>
[TEST@ora10gr1#2009-11-04/08:09:19] SQL>insert into ta values(1);1 row created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>insert into tb values(2);1 row created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>insert into tc values(11);1 row created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>
[TEST@ora10gr1#2009-11-04/08:09:19] SQL>create or replace trigger ta_up after update on ta
2 begin
3 update TB set tbID = 1;
4 end;
5 /Trigger created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>
[TEST@ora10gr1#2009-11-04/08:09:19] SQL>create or replace trigger tb_up after update on tb
2 begin
3 update TC set tcID = 1 where tcID = (Select taID from TA where taID > 10 and rownum = 1)
4 end;
5 /Trigger created.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from ta; TAID
----------
1[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from tb; TBID
----------
2[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from tc; TCID
----------
11[TEST@ora10gr1#2009-11-04/08:09:19] SQL>
[TEST@ora10gr1#2009-11-04/08:09:19] SQL>update ta set taid = 11;1 row updated.[TEST@ora10gr1#2009-11-04/08:09:19] SQL>
[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from ta; TAID
----------
11[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from tb; TBID
----------
1[TEST@ora10gr1#2009-11-04/08:09:19] SQL>select * from tc; TCID
----------
1我没有试出有什么问题呀。
去掉FOR EACH ROW改成表级的
如果是行级的,不能在触发器中查询或修改该表