ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用触发器解决更新主键冲突的问题

利用触发器解决更新主键冲突的问题

原创 Linux操作系统 作者:yangtingkun 时间:2012-04-20 23:57:33 0 删除 编辑

有朋友问我能否用触发器实现更新数据时,如果发现主键已经存在,则将冲突的主键更新为当前记录之前的主键值。

 

 

简单的说,如果表中存在主键为12的记录,如果一条UPDATE语句将1更新为2,那么想要实现的功能是为了确保这个UPDATE可以执行成功,在后台自动将ID2的记录更新为1

这个功能应该可以实现,但是直接在触发器中肯定无法实现,因为这是一个标准的变异表问题。而且即使是采用自治事务的方式,也需要小心,因为要修改原有的记录就必须通过UPDATE实现,而这个UPDATE又会导致触发器的触发,如果处理不当,就会导致循环触发。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30), CONSTRAINT PK_T PRIMARY KEY (ID));

Table created.

SQL> CREATE OR REPLACE PROCEDURE P_UPDATE_T (P_NEW NUMBER, P_OLD NUMBER) AS
  2  BEGIN
  3     FOR I IN (SELECT ID FROM T WHERE ID = P_NEW) LOOP
  4             UPDATE T SET ID = P_OLD WHERE ID = I.ID;
  5     END LOOP;
  6  END;
  7  /

Procedure created.

SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB WHERE ROWNUM < 5;

4 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE TRIGGER TRI_B_T
  2  BEFORE UPDATE ON T
  3  FOR EACH ROW
  4  BEGIN
  5     P_UPDATE_T(:NEW.ID, :OLD.ID);
  6  END;
  7  /

Trigger created.

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         2 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE

SQL> UPDATE T SET ID = 3 WHERE ID = 2;
UPDATE T SET ID = 3 WHERE ID = 2
       *
ERROR at line 1:
ORA-04091: table TEST.T is mutating, trigger/function may not see it
ORA-06512: at "TEST.P_UPDATE_T", line 3
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution of trigger 'TEST.TRI_B_T'

SQL> CREATE OR REPLACE PROCEDURE P_UPDATE_T (P_NEW NUMBER, P_OLD NUMBER) AS
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4     FOR I IN (SELECT ID FROM T WHERE ID = P_NEW) LOOP
  5             UPDATE T SET ID = P_OLD WHERE ID = I.ID;
  6     END LOOP;
  7     COMMIT;
  8  END;
  9  /

Procedure created.

SQL> UPDATE T SET ID = 3 WHERE ID = 2;
UPDATE T SET ID = 3 WHERE ID = 2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST.P_UPDATE_T", line 5
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution of trigger 'TEST.TRI_B_T'
ORA-06512: at "TEST.P_UPDATE_T", line 5
ORA-06512: at "TEST.TRI_B_T", line 2
ORA-04088: error during execution of trigger 'TEST.TRI_B_T'

正如前面提到的,如果直接修改则碰到变异表问题,如果想要通过自治事务解决变异表的问题,又会导致自身的死锁问题。

为了解决这个问题,需要仿照变异表的常规处理方法来解决。常规处理方法是通过一个包记录变量,加上BEFOREAFTER两个行级触发器以及一个语句级触发器的共同配合。但是一般而言,变异表的处理并不会引入本身记录的更新,而当前还需要解决更新当前表引发的无限循环问题。

SQL> CREATE OR REPLACE PACKAGE P_VAR AS
  2  G_STATUS NUMBER := 0;
  3  END;
  4  /

Package created.

SQL> CREATE OR REPLACE FUNCTION F_GET_T (P_ID NUMBER) RETURN NUMBER AS
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3  V_RESULT NUMBER;
  4  BEGIN
  5     SELECT COUNT(*) INTO V_RESULT FROM T WHERE ID = P_ID;
  6     RETURN V_RESULT;
  7  END;
  8  /

Function created.

SQL> CREATE OR REPLACE TRIGGER TRI_B_T
  2  BEFORE UPDATE ON T
  3  FOR EACH ROW
  4  DECLARE
  5  V_RESULT NUMBER;
  6  BEGIN
  7     V_RESULT := F_GET_T(:NEW.ID);
  8     IF V_RESULT = 1 THEN
  9             :NEW.ID := -:NEW.ID;
 10             P_VAR.G_STATUS := 1;
 11     END IF;
 12  END;
 13  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER TRI_A_T
  2  AFTER UPDATE ON T
  3  FOR EACH ROW
  4  DECLARE
  5  V_JOB NUMBER;
  6  BEGIN
  7     IF P_VAR.G_STATUS = 1 THEN
  8             DBMS_JOB.SUBMIT(V_JOB, 'BEGIN
  9             UPDATE T SET ID = ' || :OLD.ID || ' WHERE ID = ' || (-1 * :NEW.ID) || ';
 10             COMMIT;
 11             UPDATE T SET ID = ' || (-1 * :NEW.ID) || ' WHERE ID = ' || :NEW.ID || ';
 12             COMMIT;
 13             END;', SYSDATE);
 14             P_VAR.G_STATUS := 0;
 15     END IF;
 16  END;
 17  /

Trigger created.

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         2 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE

SQL> UPDATE T SET ID = 3 WHERE ID = 2;

1 row updated.

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
        -3 BRIDGE_CROSSING
         3 C_NORMAL
         4 C_SINGLE

SQL> SELECT WHAT FROM USER_JOBS;

WHAT
-----------------------------------------------------------------------------------
BEGIN
                UPDATE T SET ID = 2 WHERE ID = 3;
                COMMIT;
                UPDATE T SET ID = 3 WHERE ID = -3;
                COMMIT;
                END;

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 BIN$uo1flhazQQngRDzZK0pZWw==$0
         3 BRIDGE_CROSSING
         2 C_NORMAL
         4 C_SINGLE

最终问题通过利用包变量记录是否存在冲突记录,配合异步JOB的方式对冲突记录进行修改的方式避免了无限UPDATE的情况。

当然上面只是一个示例,对于需要同时更新多条记录的情况,需要在包中构建一个类似映射表的结构,从而记录每条更新的ID是否会出现NEW.ID存在的情况。

而且在提交的JOB中也应该对代码做更多的保护,比如添加异常处理等。

此外,这个问题的最大缺点在于,只有COMMIT后才能达到预期目标,而在更新之后且没有提交之前,更新的结果并不是UPDATE语句要完成的操作,而只是一个中间结果。

事实上,这个问题本来就不应该由触发器来解决,对于更新现有ID到一个已经存在的ID的操作,应该通过一段PL/SQL代码来实现,这样才能更好的考虑并发,事务一致性和事务完整性。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-721986/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORA-600(6002)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10390173