Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Question Unanswered: Oracle trigger confusion

    Hi there,

    I have a question about something I read in an Oracle guide (I'm fairly new to Oracle and know next to nothing about triggers):

    "Do not create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has run out of memory."

    Is this true of ANY 'after update then update' style trigger? I want to make a trigger that only fires when a column is updated with a particular value. I then want the trigger to update some other related rows in the same table. E.g.

    If I issue: UPDATE Tbl_Blah
    SET UpdateStatus='GO'
    WHERE...

    Then I want the trigger to look at the record I've just updated, taking note of the values in three of the other columns and look for the other records in the same table that have these same values. When it finds any of these records I want it to update each of their 'UpdateStatus' columns to 'GA'.

    Is this possible? Could someone give me some pointers on how to go about this, or any good internet references on trigger syntax please?

    Much appreciated,
    Gillian Klee

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle trigger confusion

    It is certainly possible, though triggers can be tricky. As long as your recursive update comes to an end (runs out of rows to update) then it should be OK.

    In this case, the trigger could have a WHEN clause:

    WHEN NEW.updatestatus = 'GO'

    Now, when the trigger fires and sets the UpdateStatus to 'GA' on some other records, the trigger will not fire recursively, because the WHEN clause will not be satisfied for the updated records.

    The problem you will hit here is the "table is mutating" error, because your trigger is attempting to update the same table that was being updated when the trigger fired. This can be overcome with some additional complexity - see here for details:
    http://asktom.oracle.com/~tkyte/Mutate/index.html

    As for references on trigger syntax, I would suggest the Oracle documentation:

    SQL Reference:
    http://otn.oracle.com/docs/products/...6a.htm#2063900

    Application Developer's Guide (Fundamentals):
    http://otn.oracle.com/docs/products/...g13trg.htm#376

  3. #3
    Join Date
    Sep 2002
    Posts
    2

    Question

    Thanks very much for your reply. Another question though:

    Is there a way I can find out which record has fired this trigger, from within the trigger? (i.e. I'll have just updated one and only one record which, if its UpdateStatus='GO', will fire my trigger.)

    I'd like my trigger to be something along the lines of:

    CREATE OR REPLACE TRIGGER Trg_UpdateStatus
    AFTER UPDATE OF UpdateStatus
    FOR EACH ROW
    WHEN (NEW.UpdateStatus='GO')
    BEGIN
    UPDATE Tbl_Unit
    SET UpdateStatus='GA'
    WHERE... UnitName is the same as the record I've just updated
    AND... WebpageID " "
    AND... WebsiteID is anything but the record I've just updated
    END;
    /

    Also, in your reply you referred to a problem with mutating tables...am I correct in thinking that since my trigger will only go off after ONE update of the table rather than multiple updates (and therefore the table is in a consistent state again), that I don't have to deal with the 'mutating' issue?

    Thanks again,
    Gillian

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by clover2411
    Is there a way I can find out which record has fired this trigger, from within the trigger? (i.e. I'll have just updated one and only one record which, if its UpdateStatus='GO', will fire my trigger.)
    Yes, you can use :OLD.UnitName etc.

    Originally posted by clover2411
    Also, in your reply you referred to a problem with mutating tables...am I correct in thinking that since my trigger will only go off after ONE update of the table rather than multiple updates (and therefore the table is in a consistent state again), that I don't have to deal with the 'mutating' issue?
    No, you will get the mutating table problem - try it and see!
    The only case (I believe) where you don't get a mutating problem is an INSERT...VALUES.
    Last edited by andrewst; 02-16-03 at 09:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •