我必须在插入时为表编写一个触发器,该触发器将根据是否;供体";已经做出了";承诺";。我尝试了几种不同的方法,我能做的最好的事情是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;
实际的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;
任何时候,只要你设法给自己带来一个变化表错误,你就会有一个设计缺陷(另请参阅Ask Tom,旧的,但仍然有效(。在这种情况下,您不应该使用触发器,而应该在发出insert语句之前在业务逻辑层中确定并设置tr_firstpledge。然而,如果你坚持使用触发器,那么你就想要一个复合触发器。这避免了错误,但在多用户环境中仍可能出现问题。
create or replace trigger first_pledge_ctrig
for insert on dd_pledge
compound trigger
k_is_first_pledge constant dd_pledge.tr_firstpledge%type := 'Y';
k_is_first_not_pledge constant dd_pledge.tr_firstpledge%type := 'N';
type doners_type is table of dd_pledge.iddonor%type;
v_doners doners_type := doners_type();
before each row is
begin
v_doners.extend;
v_doners (v_doners.count) := :new.iddonor;
:new.tr_firstpledge := k_is_first_not_pledge;
end before each row;
after statement is
begin
forall i_doner in 1 .. v_doners.count
update dd_pledge p1
set tr_firstpledge = k_is_first_pledge
where p1.iddonor = v_doners(i_doner)
and not exists
( select null
from dd_pledge p2
where p1.iddonor = p2.iddonor
and p1.rowid != p2.rowid
);
end after statement;
end first_pledge_ctrig;
这完全避免了在每一行处理过程中实际访问表(这是导致可变表错误的原因(。通常情况下,你可以选择逃脱惩罚,但在某些情况下则不然。看这里的小提琴
它的作用:
- 声明部分:设置并初始化要保存的集合随后的iddonor值。它创建了几个常量
- 前一行部分:保存上面的当前iddonor提到的集合。它还根据假设随着时间的推移,你会有更多的重复捐赠者而不是新捐赠者一个
- 最后,陈述后部分:撤销对"Y"的第一个承诺那些保存的iddonor值没有预先承诺