mutation in Trigger

Question

Post reply

mutation in Trigger

Minnu

SSCertifiable

Points: 6451
More actions
March 28, 2013 at 6:09 am

#272275

Hi Team,
Am a sql developer and new to oracle, i was assinged a trigger for
am having two tables
Table1 : Test
Cols : ID | DESC
Table2: TEST_CUST
Cols : MID | STATUS
and i want a update in third table "S_ID" when an insert | Delete | Udpate happends on Table1.
Table3 : SID
Cols : M_ID | STATUS
CREATE OR REPLACE TRIGGER TRG_TEST
AFTER INSERT OR UPDATE OR DELETE
OF ID,DESC
ON TEST
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
UPDATE S_ID SET STATUS ='Y'
WHERE SID in
(SELECT SID FROM TEST_CUST WHERE M_ID=:old."ID");
END;
--
But am gettting error "A mutation table is defined as a table that is changing"
need your help please...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your trigger is on the table TEST, but the update command is looking at a different table;

UPDATE S_ID SET STATUS ='Y'
WHERE SID in
(SELECT SID FROM TEST_CUST WHERE M_ID=:old."ID");

shouldn't that be TEST and not TEST_CUST?

otehrwise, you need to join TEST to TEST_CUST, and update SID from that combined results.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Minnu SSCertifiable Points: 6451 More actions · Answer 2

My requirement is when ever any insert|update|delete happens on Table1:Test,

then it should check the ID value in table SID, if ID is avaiale then update the column "status" to Y

can u please help in providing the query...