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

Thanks, thats what I have ended up doing :)

g

"Ralf Pickart" <ralf.pickart_at_gmx.net> wrote in message news:78ef525e.0111070735.257c246f_at_posting.google.com...
> 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 Mon Nov 12 2001 - 06:45:11 CST