Referrential integrity

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 Tue Nov 04 1997 - 00:00:00 CST