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

George Styles 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

Mutating occurs on ROW level triggers. The trick here is delay the work until a statement-after trigger. This means something akin to:

statement-before:

• initialise a pl/sql table

row-before (or after):

• store appropriate details for each row in the pl/sql table

statement-after:

• loop thru the pl/sql table and process the rows accordingly

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."