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

"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 Wed Nov 07 2001 - 11:53:42 CST