Re: Deleting with Instead Trigger in Oracle 8i

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.

As for the deletes...

Strikes me the code should read...

If inserting...
Elsif updating...
Else
do the delete stuff.

But I'm no application developer, so wait until Norman agrees with me (or tears me to shreds... he says, with Christmas Card list in hand at the ready, for retribution)...

;-)
Regards
HJR "ao52875" <ao52875_at_excite.com> wrote in message news:b7c23792.0304151907.3e2acbd3_at_posting.google.com...
> I am having a little issue when I run the Instead trigger I created.
> When I run the trigger against the view created, I get the
> confirmation that the row has been deleted. I then check to make sure
> it is gone, but it still exists. The trigger inserts and updates fine,
> but when it comes to removal of the row, there seems to be a problem.
> I checked the delete code in sqlplus to make sure the code is correct
> and deletions go according to plan.
> Any insight on my little issue would be greatly appreciated.
> Thanks in advance.
> Alex-
>
> The trigger code goes as follows:
>
> CREATE OR REPLACE TRIGGER contracttrig
> INSTEAD OF INSERT OR UPDATE OR DELETE ON contractorsview
> DECLARE
> CURSOR get_con_ID(got_con_ID IN NUMBER)
> IS
> select conID
> from contractors
> where conID = got_con_ID;
> keep_con_ID NUMBER;
>
> BEGIN
> OPEN get_con_ID(:new.conID);
> FETCH get_con_ID INTO keep_con_ID;
>
> IF INSERTING THEN
> INSERT INTO contractors( conID,
> conFirstName,
> conLastName,
> conAddress,
> conAddress2,
> conCity,
> conState,
> conZipCode,
> conPhone#,
> conCell#,
> conFax#,
> conEmail)
> VALUES( :new.conID,
> :new.conFirstName,
> :new.conLastName,
> :new.conAddress,
> :new.conAddress2,
> :new.conCity,
> :new.conState,
> :new.conZipCode,
> :new.conPhone#,
> :new.conCell#,
> :new.conFax#,
> :new.conEmail);
>
> INSERT INTO contractorsMV( conIDMV,
> conSpeciality)
> VALUES( :new.conID,
> :new.conSpeciality);
> ELSIF UPDATING THEN
> UPDATE contractors
> SET
> conFirstName = :new.conFirstName,
> conLastName = :new.conLastName,
> conAddress = :new.conAddress,
> conAddress2 = :new.conAddress2,
> conCity = :new.conCity,
> conState = :new.conState,
> conZipCode = :new.conZipCode,
> conPhone# = :new.conPhone#,
> conCell# = :new.conCell#,
> conFax# = :new.conFax#,
> conEmail = :new.conEmail
> WHERE conID = :new.conID;
>
> UPDATE contractorsMV
> SET
> conSpeciality = :new.conSpeciality
> WHERE conIDMV = :new.conID;
>
> ELSIF DELETING THEN
> DELETE FROM contractorsMV
> WHERE conIDMV = :new.conID;
>
> DELETE FROM contractors
> WHERE conID = :new.conID;
> END IF;
> END;
> /
Received on Tue Apr 15 2003 - 22:21:30 CDT