时间戳

首页 > 解决方案 > 卡在 PL/SQL 触发器的分配上

sql - 卡在 PL/SQL 触发器的分配上

问题描述

我必须在插入时为表编写一个触发器，该触发器将根据“捐助者”是否已经做出“承诺”来自动设置表的列之一的值。我尝试了几种不同的方法，我能做的最好的方法是 1 个触发器导致直接递归，另外 3 个触发器都导致了变异表错误。我正处于我所做的一切似乎只会让情况变得更糟并且找不到解决方案的地步。有人请帮忙。

桌子：

CREATE TABLE DD_Pledge (
idPledge number(5),
idDonor number(4),
Pledgedate DATE,
Pledgeamt number(8,2),
idProj number(5),
idStatus number(2),
Writeoff number(8,2),
paymonths number(3),
Campaign number(4),
Firstpledge char(1),
CONSTRAINT pledge_id_pk PRIMARY KEY(idPledge),
CONSTRAINT pledge_idDonor_fk FOREIGN KEY (idDonor) REFERENCES dd_donor (idDonor),
CONSTRAINT pledge_idProj_fk FOREIGN KEY (idProj) REFERENCES dd_project (idProj),
CONSTRAINT pledge_idStatus_fk FOREIGN KEY (idStatus) REFERENCES dd_status (idStatus));

递归触发：

CREATE OR REPLACE TRIGGER firstpledge_tr
    BEFORE INSERT ON dd_pledge
    FOR EACH ROW
DECLARE
    tr_firstpledge dd_pledge.firstpledge%TYPE;
    iddonor_count INTEGER;
BEGIN  
    SELECT COUNT(iddonor) INTO iddonor_count FROM dd_pledge WHERE iddonor = :NEW.iddonor;
    IF iddonor_count > 0 THEN
        tr_firstpledge := 'N';
    ELSE 
        tr_firstpledge := 'Y';
    END IF;
    INSERT INTO dd_pledge(idpledge,iddonor,pledgedate,pledgeamt,idproj,idstatus,writeoff,paymonths,campaign,firstpledge)
        VALUES (:NEW.idpledge,:NEW.iddonor,:NEW.pledgedate,:NEW.pledgeamt,:NEW.idproj,:NEW.idstatus,:NEW.writeoff,:NEW.paymonths,:NEW.campaign,tr_firstpledge);
   -- COMMIT;
END;

突变触发器 1：

CREATE OR REPLACE TRIGGER firstpledge_tr
    AFTER INSERT ON dd_pledge
    FOR EACH ROW
DECLARE
    tr_firstpledge dd_pledge.firstpledge%TYPE;
    iddonor_count INTEGER;
BEGIN  
    SELECT COUNT(iddonor) INTO iddonor_count FROM dd_pledge WHERE iddonor = :NEW.iddonor;
    UPDATE dd_pledge
            SET firstpledge = CASE WHEN iddonor_count<1 THEN 'N' ELSE 'Y' END
            WHERE idpledge = :NEW.idpledge;
END;

标签： sqloracleplsql

解决方案

无论如何，实际的 INSERT 操作都会发生。您只需要设置新值：

CREATE OR REPLACE TRIGGER firstpledge_tr
    BEFORE INSERT ON dd_pledge
    FOR EACH ROW
DECLARE
    tr_firstpledge dd_pledge.firstpledge%TYPE;
    iddonor_count INTEGER;
BEGIN  
    SELECT COUNT(iddonor) INTO iddonor_count FROM dd_pledge WHERE iddonor = :NEW.iddonor;
    IF iddonor_count > 0 THEN
        tr_firstpledge := 'N';
    ELSE 
        tr_firstpledge := 'Y';
    END IF;
    :NEW.firstpledge := tr_firstpledge;
END;

推荐阅读