Get :NEW table in BEFORE DELETE

Question

When creating a Trigger in Oracle there is an option for using new and old values if the Trigger is FOR EACH ROW.

I need this for the whole table, because I not use FOR EACH ROW.

I have two tables: Table1: User_Hist (ID, EID, NAME, VALID_FROM) Table2: User (ID, NAME, VALID_FROM)

In Table2 is a foreign key constraint USER(ID, VALID_FROM) REFERENCES USER_HIST(EID, VALID_FROM)

The User_Hist Table stores all Information about the User over time. The User Table should only store newest of User.

If the newest Row is deleted, the constraint will stop this.

So my Idea is to update Table User in a TRIGGER.

In the BEFORE DELETE Trigger of User_Hist will be a MERGE statement which updates the Table User. But I need which rows are deleted, best would be a :NEW table.

Can someone follow my explanation...

Maybe this help

User_hist:
ID...EID.....NAME.................VALID_FROM
1.....1.......Sarah Summer.....2010-01-01
2.....1.......Sarah Miller..........2013-01-01

USER before Delete of ID2:
ID.....NAME..............VALID_FROM
1......Sarah Miller.....2013-01-01

USER after Delete of ID2:
ID.....NAME..................VALID_FROM
1......Sarah Summer.....2010-01-01

But please keep in mind, that multiple rows of single/multiple users can be deleted.

Answer 1

You would need a compound trigger like this:

CREATE OR REPLACE TRIGGER user_trigger
   FOR DELETE ON USER_HIST
   COMPOUND TRIGGER

TYPE IdTableType IS TABLE OF USER_HIST.EID%TYPE;
IdTable IdTableType;

BEFORE STATEMENT IS   
    -- Initialize IdTable 
    BEGIN       
        IdTable := IdTableType();
    END BEFORE STATEMENT;

BEFORE EACH ROW IS   
    -- Add deleted rows to IdTable 
    BEGIN       
        IdTable.EXTEND;
        IdTable(IdTable.LAST) := :OLD.EID;
    END BEFORE EACH ROW;

AFTER STATEMENT IS 
    -- Process each row after statement
    BEGIN
       FOR i IN IdTable.FIRST..IdTable.LAST LOOP
           -- Do whatever you need for your row
           ...
           WHERE EID = IdTable(i);
       END LOOP;
    END AFTER STATEMENT;    

END;
/

Perhaps you have to add more columns from table USER_HIST in order to run your requirements. But I assume you got the principle.