mutating table - alternative workaround?

[sukimac]

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.

[kpruza]

trie to make it with a link to the same instance

[sukimac]

kpruza -

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

[kmesser]

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.