Re: Mutating Tables and RI Constraints

A copy of this was sent to jpeter5558_at_aol.comNOABUSE (JPeter5558) (if that email address didn't require changing) On 08 Aug 1999 01:12:46 GMT, you wrote:

>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

See the URL in my .sig. I have a HOWTO paper on this subject...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 09 1999 - 10:21:09 CDT