Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Deleting with Instead Trigger in Oracle 8i

Deleting with Instead Trigger in Oracle 8i

From: ao52875 <ao52875_at_excite.com>
Date: 15 Apr 2003 20:07:06 -0700
Message-ID: <b7c23792.0304151907.3e2acbd3@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:07:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US