mutating table - alternative workaround?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: mutating table - alternative workaround?

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    I'm receiving the infamous mutating table error when a trigger is fired. I have seen the various workarounds and I don't think they apply for my scenario. Basically, the problem is that the trigger fires another trigger - Here's the pseudo-code.

    before update of user.email on table user
    update team
    set team.email = user.email where user.id = team.id

    So, that was no big deal to set. It was working fine. Here's the problem. I've set up a trigger on team that needs to query user, thus, the dreaded mutating table error.

    before update of team
    where team.email = emp.email
    set team.id = user.id

    From the applications point of view these triggers should be fired b/c of separate actions. From the database point of view, the second trigger gets fired b/c the first one calls an update of team.

    Any suggestions for a workaround? Thanks for the help.


  2. #2
    Join Date
    Feb 2001
    Posts
    4
    trie to make it with a link to the same instance
    Alejandro Hanashisha A.
    Database Administrator

  3. #3
    Join Date
    Aug 2000
    Posts
    132
    kpruza -

    thx for your reply - not sure I understand your suggestion, could you supply an example?

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    create a package which creates/initializes a PL/SQL table of records which contain user.id and user.email columns.

    In a before statement trigger on update of user.email:
    create/initialize the table of records.

    In the row trigger you have already created:
    don't do the "update team". Instead, populate the PL/SQL table of records with the user.id and user.email of the rows to be updated.

    In an after statement trigger:
    process the PL/SQL table of records, updating team as necessary.

    This is the same as all the other mutating table problems. The challenge here is determining the appropriate time to create/process the table of records.
    Oracle DBA and Developer

Posting Permissions

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



Click Here to Expand Forum to Full Width