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

Hi,

just to let you know that this works great. Many thanks

George Styles

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3BE93387.490D_at_yahoo.com...
> 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..."
Received on Mon Nov 12 2001 - 06:43:57 CST