[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trigger - Update cascade referential integrity

Posted on 2006-05-24
6
Medium Priority
?
1,376 Views
Last Modified: 2008-01-09

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.
0
Comment
Question by:simonm_jp
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:mirko-iras-si
ID: 16758748
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
 

Author Comment

by:simonm_jp
ID: 16758898
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
 
LVL 16

Accepted Solution

by:
MohanKNair earned 2000 total points
ID: 16759110
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:simonm_jp
ID: 16759231
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
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 2000 total points
ID: 16759305
>> 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
 

Author Comment

by:simonm_jp
ID: 16759359
Thanks Mohan, nice work! :)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question