Re: Referrential integrity

You can compilation with 'Show errors trigger <triggername>'

Alex Vilner wrote:

> Hello,
>
> We are using Oracle 8 for the database, and there are a few issues
> that we
> ran into with enforcing the refferential integrity that we would like
> to
> bounce off of Oracle gurus.
>
> 1) When updating a table, we would like to be able to analyze other
> information inside the same table (stored in other rows). When issuing
> a
> SELECT against the table in-transition, we get the infamous "Mutating
> table" message. The trigger being defined as either BEFORE or AFTER
> does
> not make a difference. Is there an alternative way of doing this?
>
> 2) When two tables are joined in the primary-foreign key relationship,
>
> cascading deletes or restricting deletes work fine. There is no way,
> to our
> best knowledge, to specify that the dependent table should have its
> foreign
> key column set to NULL when primary is deleted.
>
> 3) In the situation with the primary-foreign keys another issue we are
>
> facing is when upon inserting into the primary table we need to insert
>
> several rows into the dependent table. It seems that even when AFTER
> INSERT
> trigger is executed, the row is still NOT in the primary table, and
> so the
> refferential integrity constraint fails on an insert into dependent
> table.
>
> Oh, and finally, is there an easy way of debugging a trigger? If a
> stored
> procedure compiles with errors, one can view them using the SHOW
> ERRORs
> command (or use the Schema Manager). There is nothing we found that
> would
> do the trick for triggers.
>
> We tried declaring stored procedures, using the SQL from triggers to
> eliminate some of the compile errors, but the problem is that triggers
> also
> use some trigger-specific syntax, like :NEW and :OLD, which do not
> work
> inside SPs.
>
> Any suggestions to any of the possible workarounds would be greatly
> appreciated. Unless we are doing something radically wrong, it seems
> surprising that a famous and popular database, like ORACLE, would
> have
> such issues that cannot be easily resolved. Thank you all in advance!
>
> Alex Vilner
Received on Fri Nov 07 1997 - 00:00:00 CST