Re: Deleting with Instead Trigger in Oracle 8i

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Wed, 16 Apr 2003 22:35:25 +0100
Message-ID: <3e9dd111$1_3@mk-nntp-1.news.uk.worldonline.com>

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7038F883A_at_lnewton.leeds.lfs.co.uk...
> Morning,
>
> you MUST use :OLD in a delete trigger and not :NEW. If you use :NEW then
> you get the message that n rows have been deleted, but they have not.
> Change the trigger to the following :
>
> ELSIF DELETING THEN
> DELETE FROM contractorsMV
> WHERE conIDMV = :OLD.conID;
>
> DELETE FROM contractors
> WHERE conID = :OLD.conID;
>
>
> Cheers,
> Norm.
>

I may well have missed something, but all of this seems reasonable enough to me.
From an empirical point of view, one would tend to assume that on insert, :old would be null, and :new contains the inserted values. On update, :old and :new would reflect the obvious, and on delete :old would be the 'before image', whereas :new would be null.

Isn't that how it works?

Regards,
Paul Received on Wed Apr 16 2003 - 16:35:25 CDT