Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Another mutating table question

    We have a situation where a user may update a flag to 'Y'. In this situation, we want to check other records in that table which bear the same foreign key value, and if any of those are already set to 'Y', rewrite the user's entry to a NULL.

    I have the idea in my head pseudocoded as:
    Code:
    BEFORE INSERT OR UPDATE ON EACH ROW
    if :NEW.flag = 'Y' Then
      populate array with that row
    
    AFTER INSERT OR UPDATE 
    foreach item in array
      check other items in the table with the same foreign key
      if those other records share this value then set flag = NULL using
          update rcd_flagged t1
          set    t1.flag = NULL 
          where  t1.tk = rcd_flagged_row.tk and
    	     exists (select 1 
                        from rcd_flagged t2 
    		    where t2.flag = 'Y' and 
                              t1.fk = t2.fk and
                              t1.tk <> t2.tk);
    The problem (I think) is that the update in the after statement trigger is causing the before each row trigger to fire recursively (I get the error: SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded)).

    Is there a standard way of getting this to work? I have in my mind a PACKAGE variable which tracks whether the update is coming in from the after statement trigger, and then jumping out of the before each row trigger, but I don't see how that would get me out the 'recursive-ness'.

    -Chuck

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Why not just write a PROCEDURE/PACKAGE?

    check FK, if 'Y' FOUND then update row to NULL
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    If a PACKAGE had to be added to the mix, then you'd have to keep track of the PK for the record entered and then pass that into the stored procedure, so I guess you'd always have a 2-step process. That would have to be something that's remembered in any other situation. If it's all enclosed in the table's triggers, then the business rules are centralized.

    -Chuck

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Create a view and an INSTEAD OF trigger!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Nice.

    I amended your suggestion a bit so that a PACKAGE SPEC variable must be set to a certain value (done so in the INSTEAD OF trigger), or else the BEFORE...FOR EACH ROW trigger raises a raise_application_error, therefore forcing all updates/inserts via the view.

    Thanks,
    Chuck

Posting Permissions

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