Stuck on an assignment for PL/SQL Triggers

Question

I have to write a trigger for a table on inserting that will auto-set the value for one of the table's columns based on if the "donor" has already made a "pledge". I have tried a few different ways and the best I could do was 1 trigger that resulted in a direct recursion and 3 others that both resulted in mutating tables errors. I am at the point where everything I do only seems to make it worse and can't find a solution. Someone please help.

Table:

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));

RECURSIVE TRIGGER:

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;

MUTATING TRIGGER 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;

Answer 1

Anytime you manage to get yourself a mutating table error, you have a design flaw (also see Ask Tom, older but still valid). In this case you should not be using a trigger but making the determination and setting the tr_firstpledge in the Business Logic layer before the insert statement is issued. However if you insist on a trigger then you want a compound trigger. Which avoids the error, but may still present trouble in a multi user environment.

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;

This completely avoids actually access table during each row processing (this is the cause of the mutating table error). Usually you can get away with select - but here are situations where not. See fiddle here.
What it does:

• The declaration section: Sets up and initializes a collection to hold the subsequent iddonor values. And it creates a couple constants.
• The before row section: Saves the current iddonor in the above mentioned collection. It also sets first pledge to 'N' under the assumption that over time you will have more repeat donors than new ones.
• Finally, the after statement section: Reverses first pledge to 'Y' for those iddonor values saved that do not have a prior pledge.

Answer 2

The actual INSERT action will happen anyway. You just need to set the NEW value:

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;