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?
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.