I have a really difficult Oracle 7 problem involving triggers and mutating
I have the following tables (simplified):
ORGS is a hierarchical table outlining the entire organization. The
trigger fires on INSERT, UPDATE and DELETE for ORGS. The affected records
and their children must insert some records in an associated table
(X500_DELETE) for the purpose of deleting names in an X500 mail directory.
My other table that I need to grab data from (PERSON) is comprised of
people's names and their organization acronym (a unique name in the mail
system). This PERSON table is linked to a STAFF table of position in a
So I had planned to have an insert statement with a subquery to gather the
data from the appropriate tables. It would be fine if we were actually
using the acronym from the ORGS entry that was changing. But instead I
need to query up and get a higher level ORGS acronym (usually level 3 of
7) - a no-no because the ORGS table is mutating.
We were going to create a statement trigger that would quickly duplicate
the ORGS table (1000 records) and use this to traverse the tree and get
the appropriate acronym. But CREATE TABLE is illegal in PL/SQL.
I'd really appreciate any suggestions.
For now I think I'm going to create a duplicate table, have a BEFORE
trigger mirror the other table, and have an AFTER trigger do what I need.
Any comments are much appreciated!