-
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.
-
trie to make it with a link to the same instance
Alejandro Hanashisha A.
Database Administrator
-
kpruza -
thx for your reply - not sure I understand your suggestion, could you supply an example?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|