Re: Is there a way around mutating tables in Oracle triggers?

Hi George,

the only way I know is to use a combination of row trigger and statement trigger together with a PL/SQL package:

The row trigger saves the modified primary key (or other data) to a PL/SQL package variable or better a PL/SQL table organized as stack.

The statement trigger (which is fired on commit) reads the package variable or the stack and does all work you want to do. The statement trigger may read the "mutating" table because it no longer mutating.

I implemented such triggers in many cases and it works fine!

Greetings
Ralf

"George Styles" <george_at_ripnetwork.fsnet.co.uk> wrote in message news:<9sb7r8$oi2$1_at_newsg4.svr.pol.co.uk>...
> Hi,
>
> We are having a problem writing a trigger on Oracle.
>
> There are 2 tables, in a master/detail relationship. The master table
> needs to have a couple of fields updated from the detail table.
>
> What we need to do is to copy one field from the most recent (as
> decided by a date field) record in detail table to a corrosponding
> field in the master table,
>
> eg
>
> Master table:
>
> SITES
> (
> ID NUMBER(10,0),
> SiteName VARCHAR
> MostRecentVisit Date
> MostRecentVisitType NUMBER(10,0)
> )
>
> Detail table:
>
> VISITS
> (
> SITEID NUMBER(10,0),
> DateOfVisit DATE,
> VisitType NUMBER(10,0)
> )
>
> What we want to do is place a trigger on the visits table, so that
> every time it is updated, it finds the most recent visit to that site
> (which is not always the one being altered) and copy back the fields
> DateOfVisit and VisitType to the master table (Sites)
>
> All our attempts so far have resulted in an error saying that Visits is
> 'mutating', and therefore cannot be queried.
>
> Has anyone got any suggestions as to how we may achieve this please?
>
> Thanks in advance
>
> George
Received on Wed Nov 07 2001 - 09:35:14 CST