CODEWITHSUNDEEP
oracle-databaseplsqlwhere-clausedatabase-triggerrowtype
krise
krise

Reputation: 535

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?

Upvotes: 1

Views: 230

Answers (2)

Radagast81
Radagast81

Reputation: 3006

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.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142768

That would probably be something like

DELETE FROM TRIGGERREF1 a WHERE a.OWNER = referencedItems.owner;
                                        ^                ^^^^^^^
                                       this              this

i.e. you'd reference a column within the rereferncedItems.

Upvotes: 2

Related Questions