快捷導(dǎo)航

Oracle使用insert觸發(fā)器遇到的問題及解決

更新時間：2024年10月15日 14:59:06 作者：世間所有相遇都是久別重逢

在數(shù)據(jù)庫管理中,觸發(fā)器是重要的工具,用于實現(xiàn)自動化操作和數(shù)據(jù)完整性維護,本文以一個實際案例詳解觸發(fā)器的基本語句和應(yīng)用,在導(dǎo)入個人信息時,如果已存在該信息且需更新非導(dǎo)入項（如疫情信息）,觸發(fā)器通過比較:new與:old屬性值實現(xiàn)自動同步

Oracle使用insert觸發(fā)器遇到的問題

首先TRIGGER基本語句

create or replace trigger 'trigger_name'--觸發(fā)器名稱
before insert --或 after insert （觸發(fā)時機）
--關(guān)鍵字before和after用于標識觸發(fā)時間，顧名思義，before代表觸發(fā)器里面的命令在DML修改數(shù)據(jù)之前執(zhí)行，
--after代表觸發(fā)器里面的命令在DML修改數(shù)據(jù)之后執(zhí)行。
REFERENCING new as new --或old as old （用于觸發(fā)器中參數(shù)使用）
--注：before中的new指的是當前插入的集，after中的old指的是當前插入的集
FOR EACH ROW --指當前觸發(fā)器為行級觸發(fā)器（行級觸發(fā)器相對于語句級觸發(fā)器）
--行級觸發(fā)器能夠通過 :new.屬性 和 :old.屬性等獲得update或者insert發(fā)生之前的新值和發(fā)生值之后的舊值。
declare --聲明變量……
BEGIN
END;

關(guān)于行級觸發(fā)器的 :new.屬性和 :old.屬性使用

TRIGGER TONGBUJIAYI_SFZH
before
 DELETE OR UPDATE of D_SFZH
 ON T_DA_JKDA_RKXZL
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
BEGIN
 IF UPDATING
 THEN
insert into T_DA_JKDA_RKXZL_GWANDJY(ID,D_GRDABH,P_RGID,D_SFZH,D_SFZHXGQ,D_DATZXGH,D_XM,D_BGSJ,D_BGZT)
VALUES (SYS_GUID(),:OLD.D_GRDABH,:OLD.P_RGID,:OLD.D_SFZH,:NEW.D_SFZH,'',:OLD.D_XM,sysdate,'2');
ELSIF DELETING
 THEN
insert into T_DA_JKDA_RKXZL_GWANDJY(ID,D_GRDABH,P_RGID,D_SFZH,D_SFZHXGQ,D_DATZXGH,D_XM,D_BGSJ,D_BGZT)
VALUES (SYS_GUID(),:OLD.D_GRDABH,:OLD.P_RGID,:OLD.D_SFZH,'','',:OLD.D_XM,sysdate,'1');
END IF;
END;

在Oracle中執(zhí)行DML語句的時候是需要顯示進行提交操作的。

當我們進行插入的時候，會觸發(fā)觸發(fā)器執(zhí)行對觸發(fā)器作用表和擴展表的種種操作，

但是這個時候觸發(fā)器和插入語句是在同一個事務(wù)管理中的，因此在插入語句沒有被提交的情況下，我們無法對觸發(fā)器作用表進行其他額外的操作。

如果執(zhí)行其他額外的操作則會拋出如下異常信息。

ORA-04091：表*****發(fā)生了變化觸發(fā)器/函數(shù)不能讀它

此次觸發(fā)器產(chǎn)生場景：

一個身份證號及個人信息導(dǎo)入到系統(tǒng)中，在系統(tǒng)中修改了關(guān)于疫情的信息。

但再次導(dǎo)入此人的信息時（每次導(dǎo)入的信息都不包含關(guān)于疫情的信息），則需要同步。

由此引入觸發(fā)器，觸發(fā)條件為insert

TRIGGER DETECTION_USERS_SYN_VACCINE
  BEFORE INSERT ON DETECTION_USERS
  REFERENCING new as new
  FOR EACH ROW

DECLARE --oracle中聲明多個屬性時只需要用一次DECLARE即可
  V_ID              DETECTION_USERS.ID%TYPE;--此處聲明的屬性是按照DETECTION_USERS表中的屬性聲明
  V_IS_VACCINATION  DETECTION_USERS.IS_VACCINATION%TYPE;
  V_VACCINE1        DETECTION_USERS.VACCINE1%TYPE;
  V_VACCINE2        DETECTION_USERS.VACCINE2%TYPE;
  V_VACCINE3        DETECTION_USERS.VACCINE3%TYPE;
  V_NUM_VACCINATION DETECTION_USERS.NUM_VACCINATION%TYPE;
  --聲明游標查詢此人添加之前的接種信息
  CURSOR latests is
    SELECT ID,
           IS_VACCINATION,
           VACCINE1,
           VACCINE2,
           VACCINE3,
           NUM_VACCINATION
      FROM (SELECT t.*, row_number() over(order by nvl(zdrq, '0') DESC) rn
              from (SELECT *
                      FROM (SELECT ID,
                                   IS_VACCINATION,
                                   VACCINE1,
                                   VACCINE2,
                                   VACCINE3,
                                   NUM_VACCINATION,
                                   (CASE
                                     WHEN (VACCINE3 IS NOT null) THEN
                                      VACCINE3
                                     WHEN (VACCINE2 IS NOT null) THEN
                                      VACCINE2
                                     WHEN (VACCINE1 IS NOT null) THEN
                                      VACCINE1
                                     ELSE
                                      ''
                                   END) AS zdrq
                              FROM DETECTION_USERS
                             WHERE "IDENTITY" = :new."IDENTITY"
                               AND IS_VACCINATION IS NOT NULL)) t)
     WHERE rn = 1;
BEGIN

  OPEN latests;
  FETCH latests
    INTO V_ID,
         V_IS_VACCINATION,
         V_VACCINE1,
         V_VACCINE2,
         V_VACCINE3,
         V_NUM_VACCINATION;
         ---打印語句
--  DBMS_OUTPUT.PUT_LINE('latests===' || V_ID || '**' || V_IS_VACCINATION || '**' ||
--                       V_VACCINE1 || '**' || V_VACCINE2 || '**' ||
--                       V_VACCINE3 || '**' || V_NUM_VACCINATION);
--  DBMS_OUTPUT.PUT_LINE('new===' || :new.ID || '**' || :new.IS_VACCINATION || '**' ||
--                       :new.VACCINE1 || '**' || :new.VACCINE2 || '**' ||
--                       :new.VACCINE3 || '**' || :new.NUM_VACCINATION);

  IF INSERTING THEN
    :new.IS_VACCINATION  := V_IS_VACCINATION;--將打開的cursor賦值到新插入的這列數(shù)值中
    :new.VACCINE1        := V_VACCINE1;
    :new.VACCINE2        := V_VACCINE2;
    :new.VACCINE3        := V_VACCINE3;
    :new.NUM_VACCINATION := V_NUM_VACCINATION;
  
  END IF;
  --FETCH NEXT FROM from_inserted INTOV_ID,V_IS_VACCINATION,V_VACCINE1,V_VACCINE2, V_VACCINE3,V_NUM_VACCINATION;
  --循環(huán)，此處cursor只有一條，不需要。
  CLOSE latests;

END;