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.
simonm_jpAsked:
Who is Participating?
 
MohanKNairConnect With a Mentor Commented:
Consider the Relation from emp to dept table. dept_id in emp references PK of dept table.
This does not work for tables with more than one column as PK (composite PK)
Assume that dept_id is a number datatype

create or replace trigger dept_trg1
before delete or update on dept
for each row
begin
IF UPDATING
THEN
for c1 IN(select table_name,column_name,position from user_cons_columns where constraint_name in(select constraint_name from user_constraints
where r_constraint_name='DEPT_PK') order by 1,3) LOOP
execute immediate 'update '||c1.table_name||' set '||c1.column_name||'='||:new.dept_id||' where '||c1.column_name||'='||:old.dept_id;
END LOOP;
ELSE
for c1 IN(select table_name,column_name,position from user_cons_columns where constraint_name in(select constraint_name from user_constraints
where r_constraint_name='DEPT_PK') order by 1,3) LOOP
execute immediate delete '||c1.table_name||' where '||c1.column_name||'='||:old.dept_id;
END LOOP;
END IF;
end;
/
0
 
mirko-iras-siCommented:
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).
0
 
simonm_jpAuthor Commented:
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,
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
simonm_jpAuthor Commented:
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,
0
 
MohanKNairConnect With a Mentor Commented:
>> why is the execute immediate delete not encased in quotes whereas the immediate 'update' is?

Type error
execute immediate 'delete '||c1.table_name||' where '||c1.column_name||'='||:old.dept_id;

>> What does the ORDER BY 1,3 do?

for tables with only one column as PK this order by is not reuired. It can be ignored
0
 
simonm_jpAuthor Commented:
Thanks Mohan, nice work! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.