利用觸發器解決更新主鍵衝突的問題

有朋友問我能否用觸發器實現更新資料時，如果發現主鍵已經存在，則將衝突的主鍵更新為當前記錄之前的主鍵值。

簡單的說，如果表中存在主鍵為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程式碼來實現，這樣才能更好的考慮併發，事務一致性和事務完整性。