Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Mutating Tables and RI Constraints

Re: Mutating Tables and RI Constraints

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Tue, 10 Aug 1999 10:23:17 -0400
Message-ID: <37B035D5.9D57382E@bigfoot.com>


How to I get to your .sig?

Thomas Kyte wrote:

> 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 Tue Aug 10 1999 - 09:23:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US