Deleting with Instead Trigger in Oracle 8i

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 16 Apr 2003 09:35:26 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7038F8835@lnewton.leeds.lfs.co.uk>

Evening Howard,

put the Christmas Card list away !!!

>> I might need practice in reading triggers, but as I read your code,
it goes
>>'If inserting.... updating... elsif deleting...'
>> Strikes me that the updating part of the code gets executed
regardless of
>> what you're actually doing... but I could be wrong.

No, the OP was correct, he had 'if inserting ... elsif updating .... elsif deleting ... end if' which is fine. Obvioulsy the final elseif could be an else on the grounds of no more options though !

One thing to mention though, if you 'create or replace view' then any instead of triggers will be silently deleted, but grants to the view will remain - it's nice to be consistant :o)

I've done a test and it does indeed fail to delete but the reason while being quite simple, is a b*gg*r to track down :

you MUST use :OLD in a delte 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.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Received on Wed Apr 16 2003 - 03:35:26 CDT