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

Thank you

George

"Ed prochak" <ed.prochak_at_alltel.com> wrote in message news:e51b160.0111070953.37f9921e_at_posting.google.com...
> "George Styles" <george_at_ripnetwork.fsnet.co.uk> wrote in message
news:<9sb7q5$qnu$1_at_newsg1.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
>
>
> If this visit is not the most recent, then you have nothing to do,
> Right?
>
> So if you query the SITES table and its MostRecentVisit > DateOfVisit
> for this visit, then do nothing,
> otherwise,
> update the SITES table.
>
> there is still a possibility of race conditions with this first
> approach.
> As an alternative, do this:
>
> create a SITES_UPDATE_QUEUE table
> where the first approach would have updated SITES, have it instead
> insert a row in the queue table. Later a periodic batch process sorts
> the queue and performs the needed updates to SITES.
>
>
> HTH
> Ed
>
> Ed Prochak
> Magic Interface, Ltd.
> http://www/magicinterface.com
Received on Thu Nov 08 2001 - 10:24:58 CST