Re: Mutating Triggers in Oracle

ctcgag_at_hotmail.com wrote in message news:<20030430112615.090$hB_at_newsreader.com>...
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote:
> > I did it thusly.
> > Created a type that is the record I want.
> > Created a table of that type. (in a package)
> > Procedures in the package
> > Clear table
> > Add a record to the table (this table is in memory)
> > Write the in memory table to where ever I wanted it.
> >
> > In the before statement trigger on table A I clear and initialize the
> > table of the type.
> > In the after row trigger I fill up the table of type with records.
> > In the after statement trigger I write the information from the table of
> > type records to wherever I wanted.
> >
> > Jim
>
> Why did you need a mutating trigger? I think I need one, but I'm trying
> to convince myself I don't, so I'm curious to know what situations
> others find themselves needing them.
>
> Xho

Hi,

I need a trigger like this because, in general, Oracle doesn't allow to start another transaction if one has been initialized, for instance, this happens in my case: after an insert/update/delete in a table T, I need to perform some selects in the same table. So, I need to use an engine like the one described before.

If you are using Oracle 8i, for example, it is possible to set a pragma autonomous_transaction flag in the declaration of your trigger to deal with this matter.

My database is Oracle 8i and my previous version of the trigger used the autonomous_transaction flag. But, as my trigger writes to a remote database,
once I got the error:

ORA-00164: autonomous transaction disallowed within distributed transaction

This is because Oracle8i does not support autonomous transactions within a distributed query.

So, I decided to change the implementation of my trigger to use a package, a row trigger and a statement trigger.

I found a good link which explains how to clear the temporary table:

http://osi.oracle.com/~tkyte/Mutate/index.html

It seems to be necessary a before trigger that resets the temporary table.

Thanks for everybody!

Best Regards!

Marcelo Received on Wed Apr 30 2003 - 20:26:08 CDT