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

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 Wed Nov 07 2001 - 10:35:36 CST