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

最新推荐文章于 2023-05-12 17:21:41 发布

ckawt40802

最新推荐文章于 2023-05-12 17:21:41 发布

阅读量348

点赞数

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

简单的说，如果表中存在主键为1和2的记录，如果一条UPDATE语句将1更新为2，那么想要实现的功能是为了确保这个UPDATE可以执行成功，在后台自动将ID为2的记录更新为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'

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-721986/