通过TRIGGER模拟约束ON UPDATE选项

Question

我正试图通过制作触发器来模拟外键中的ON UPDATE选项，该触发器将在更新后更新子记录。

触发器看起来像这样：

CREATE OR REPLACE TRIGGER SOMETABLE_AU_ID_TRG
  AFTER UPDATE OF ID ON SOMETABLE
  FOR EACH ROW
BEGIN
  UPDATE SOMECHILDTABLE SET SOMETABLE_ID = :NEW.ID WHERE ID = :OLD.ID;
END;

我正在使子表约束像这样可延迟：

set constraint 
SOMECHILDTABLE_FK_SOMETABLE deferred;

但它仍然让我提交ORA-02292 (child record found)。我该如何避免这种情况？

Answer 1

感谢@BrianCamire发现错误：

让我们从创建触发器开始。。。

SQL> CREATE OR REPLACE TRIGGER SOMETABLE_AU_ID_TRG
  AFTER UPDATE OF ID ON SOMETABLE
  FOR EACH ROW
BEGIN
  UPDATE SOMECHILDTABLE SET SOMETABLE_ID = :NEW.ID WHERE ID = :OLD.ID;
END;  2    3    4    5    6  
  7  /
Trigger created.
SQL> alter table somechildtable add constraint some_fk foreign key (sometable_id)
  2  references sometable (id) deferrable initially deferred;
Table altered.
SQL>  

这是数据。。。

SQL> select * from somechildtable;
        ID SOMETABLE_ID
---------- ------------
        11            1
        12            2
        13            6
        14            4
        15            5
SQL>

所以，让我们来处理父数据。。。。

SQL> update sometable set id = 3 where id = 6
  2  /
1 row updated.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (APC.SOME_FK) violated - child record found

SQL> 

啊哈！因此，正如Brian所说，我们需要更改触发代码：

SQL> CREATE OR REPLACE TRIGGER SOMETABLE_AU_ID_TRG
      AFTER UPDATE OF ID ON SOMETABLE
      FOR EACH ROW
    BEGIN
      UPDATE SOMECHILDTABLE SET SOMETABLE_ID = :NEW.ID WHERE SOMETABLE_ID = :OLD.ID;
    END; 
/
  2    3    4    5    6    7  
Trigger created.
SQL> SQL> update sometable set id = 3 where id = 6;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from somechildtable
  2  /
        ID SOMETABLE_ID
---------- ------------
        11            1
        12            2
        13            3
        14            4
        15            5
SQL>