Help! Attack of the Mutant Triggers!
Date: 1995/07/19
Message-ID: <???-1907952134590001_at_dd06-062.compuserve.com>#1/1
I have a really difficult Oracle 7 problem involving triggers and mutating tables.
As follows:
I have the following tables (simplified):
ORGS
ORG_ID
ACRONYM
ORG_NAME
SUPERIOR_ORG_ID
ORG_LEVEL PERSON
PERSON_ID
LAST_NAME
FIRST_NAME STAFF
PERSON_ID
ORG_ID X500_DELETE
LAST_NAME
FIRST_NAME
ORG_ACRONYM 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 given organization.
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!
Thanks
Chris Wise
RKIL Received on Wed Jul 19 1995 - 00:00:00 CEST