Search code examples
oracle-databaseplsqlwhere-clausedatabase-triggerrowtype

Using a %ROWTYPE in a WHERE IN clause


I am trying to write a trigger that deletes certain rows from my table TRIGGERREF1 when I am updating another table TRIGGERTEST1. Here is the code:

create or replace TRIGGER "DELETEBYUPDATE_TRIGGERTEST1" BEFORE UPDATE ON TRIGGERTEST1
FOR EACH ROW
DECLARE 
    referencedItems TRIGGERREF1%rowtype;
BEGIN 
    SELECT * INTO referencedItems FROM TRIGGERREF1 WHERE OWNER = :New.ID;
    EXCEPTION WHEN NO_DATA_FOUND THEN RETURN;
    DELETE FROM TRIGGERREF1 a WHERE a.OWNER IN referencedItems;
END;

The line

DELETE FROM TRIGGERREF1 a WHERE a.OWNER IN referencedItems;

Does not compile because, apparently you can't do a WHERE IN %ROWTYPE condition.

Is there a way to make this work or do I need to solve my problem without the use of %ROWTYPE?


Solution

  • Why not simply delete directly?

    create or replace TRIGGER "DELETEBYUPDATE_TRIGGERTEST1" BEFORE UPDATE ON TRIGGERTEST1
    FOR EACH ROW
    DECLARE 
    BEGIN 
        DELETE FROM TRIGGERREF1 a WHERE a.OWNER = :New.ID;
    END;
    

    Because, when there is no row matching your WHERE condition just nothing is done.