asked on

Trigger - Update cascade referential integrity

I need to write a trigger using Oracle 9i SQL that will implement an update cascade referential integrity rule
between any two tables in a database ... not sure where to start.

Any help would be appreciated.

mirko-iras-si

Hi,

I understand you mean a "universal" trigger - a single trigger that would fire on DML operations on some or all tables. Such a trigger is impossible to write in Oracle. Even Oracle 10g R1 documentation has no word about this.

You need to write one trigger for each foreign key. Or, alternatively, make a SQL script that creates all triggers based on data in the system views (user_constraints and user_cons_columns, for example).

simonm_jp

ASKER

Sorry, I may have confused you (and myself)

What I need to do is write a trigger so that if a value of the PK in the parent table is changed, then the values of the corresponding FK in the child table will also be changed automatically.

Let me know if that helps.

Cheers,

ASKER CERTIFIED SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

simonm_jp

ASKER

Thanks :) ... now so I understand it ... :|

What does the ORDER BY 1,3 do?

It seems you are executing a string by the method: execute immediate ... why is the execute immediate delete not encased in quotes whereas the immediate 'update' is?

Thanks,

SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

simonm_jp

ASKER

Thanks Mohan, nice work! :)