Help! Attack of the Mutant Triggers!

Help! Attack of the Mutant Triggers!

Post by Chris Wi » Thu, 20 Jul 1995 04:00:00



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

 
 
 

Help! Attack of the Mutant Triggers!

Post by Will Kooiman, Computer Systems Authorit » Sun, 23 Jul 1995 04:00:00


I don't think I can help with your mutating table, but I believe you CAN do DDL from PL/SQL.

I've seen a package in the oracle_home/rdbms/admin directory that appears to do it.  I think the
name of the package is dbms_ddl.

Note, I have not used this package, nor do I know anyone that has, so I'm not sure if it actually
does ddl.

Also, in Oracle 7.2, they're supposed to allow dynamic sql from PL/SQL.  You could execute the
create table statement via dynamic SQL.

Will Kooiman.

 
 
 

Help! Attack of the Mutant Triggers!

Post by am.. » Fri, 04 Aug 1995 04:00:00


Quote:> Also, in Oracle 7.2, they're supposed to allow dynamic sql from PL/SQL.

 You could execute the

Quote:> create table statement via dynamic SQL.

> Will Kooiman.

You can do dynamic sql also in version 7.1 using dbms_sql package !!

Ami.

 
 
 

1. Mutant tables in read-only Triggers

Hello,

I am having problem with mutant tables, even if I dont change
anything inside the trigger. The message is "ORA-04091 table xxx is
mutating, trigger/function may not see it"

I want to use an after insert/update trigger to check this and I need
to use the updated fields in SELECT statement.  I don't want to use a
before trigger.

Lets see an simple example:
Suppose I want to avoid a single customer to get more then $ 500 of
credit.

create or replace trigger CredLimit
  After Insert or Update on AccountMoviment
  For each row  
  declare TotalValue Integer;        
  Begin
     select sum(AccountMoviment.Value) into TotalValue
     from AccountMoviment
     where :new.CustomerId = AccountMoviment.CustomerId ;
     if (TotalValue > 500) then
        Raise_Application_Error (-20000, 'Limit is $ 500');
     end if;
  End CredLimit;

I also rewrite the above trigger using DECLARE CURSOR and Sub
Transactions. The result was the same. In all cases, trigger
compilation was OK, but not run.

My questions are:

As my trigger don't insert/update/delete anything, why I can't use the
updated fields in a simple SELECT statement ?

Is there a way to overcome this oracle message ?

Thanks in advance

Marcos Augusto

2. New game -- eject doesn't work

3. Advertisement by Oracle about two phase commit attacking SYBASE.

4. serial over ethernet How ???

5. Janitors Protest Attacked by Securi

6. ADAT

7. Help Help Help Help Help Help Help Help Help Help Help Paleeeeeezzzzzzz!!!!!!!!

8. Email Error

9. HELP HELP : ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist

10. Converting Forms2.3 Triggers To 3.0 PLsql Triggers

11. Trigger Problem -- using column on triggered table

12. ALTERing triggers from within a trigger

13. Trigger to update triggering table