Mutating Tables and RI Constraints

Hi,

I have a table containing a key/status/effective date combination. When status changes, the values are propagated by trigger to a log table recording the status history. When no RI constraint is in place everything works fine.

When an RI trigger enforces a parent-child relationship, the status change logging trigger fails because the parent table is mutating. Propagating the values to the log table implicitly generates a lookup back to the parent table to ensure the RI constraint is satisfied.

I do not want to drop the RI constraint. I realize that the status is denormalized. I want it that way. What is a good way to maintain the log?

Oracle documentation suggests that the status log trigger can be split into a row level trigger which saves the critical values to a PL/SQL package table and then an statement level trigger can read the values out of the PL/SQL table and propagate them to the log.

Any comments on how well this or any other approach works are greatly appreciated. I'm sure others who use log or history tables must have encountered this same situation.

Thanks,

Jim
jpeter5558_at_aol.com Received on Sat Aug 07 1999 - 20:12:46 CDT