mutation in Trigger

  • Minnu

    SSCertifiable

    Points: 6451

    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...

  • Lowell

    SSC Guru

    Points: 323349

    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

    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...

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

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