Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Deleting with Instead Trigger in Oracle 8i
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;