Is there a way around mutating tables in Oracle triggers?

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 Received on Wed Nov 07 2001 - 05:59:29 CST