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

Thanks for your response.

g

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3be96279.66882140_at_news.alt.net...
> On Wed, 7 Nov 2001 11:59:59 -0000, "George Styles"
> <george_at_ripnetwork.fsnet.co.uk> wrote:
>
> >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
>
> Couldn't you implement that as an AFTER UPDATE trigger?
>
> If the current row being added is of no consequence, would adding
> PRAGMA AUTONOMOUS_TRANSACTION help?
>
> Brian
Received on Mon Nov 12 2001 - 06:45:19 CST