Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

mutation in Trigger Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 6:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 4, 2014 4:34 AM
Points: 233, Visits: 739
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...
Post #1436424
Posted Thursday, March 28, 2013 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436426
Posted Thursday, March 28, 2013 6:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 4, 2014 4:34 AM
Points: 233, Visits: 739

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...
Post #1436434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse