Home » SQL & PL/SQL » SQL & PL/SQL » deleting row in one table using another table
deleting row in one table using another table [message #423356] Wed, 23 September 2009 10:40 Go to next message
user123
Messages: 5
Registered: September 2009
Junior Member
I have 3 tables in Oracle.

table1 consists of following fields where emp_id is the primary key.
emp_id,emp_name

table2 consists of following fields
emp_id, salary

table3 consists of following fields
emp_id,dependent_name

If I delete one of the employee id from emp_id column of table1,then all data related to that employee id that is contained in the table2,table3 must be deleted.

How to do that in Oracle?



Re: deleting row in one table using another table [message #423357 is a reply to message #423356] Wed, 23 September 2009 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How to do that in Oracle?
By implementing an ON DELETE trigger.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: deleting row in one table using another table [message #423359 is a reply to message #423356] Wed, 23 September 2009 12:00 Go to previous messageGo to next message
user123
Messages: 5
Registered: September 2009
Junior Member
what is the other alternative without using trigger?
Re: deleting row in one table using another table [message #423360 is a reply to message #423356] Wed, 23 September 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
DELETE FROM TABLE1 WHERE EMP_ID = 123;
DELETE FROM TABLE2 WHERE EMP_ID = 123;
DELETE FROM TABLE3 WHERE EMP_ID = 123;
COMMIT;
Re: deleting row in one table using another table [message #423361 is a reply to message #423359] Wed, 23 September 2009 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take one as the master table, put a FK on the other tables with option ON DELETE CASCADE.

Regards
Michel
Re: deleting row in one table using another table [message #423386 is a reply to message #423361] Wed, 23 September 2009 21:48 Go to previous message
user123
Messages: 5
Registered: September 2009
Junior Member
Thank you Michel Cadot and BlackSwan.
Previous Topic: Query tunning
Next Topic: storing images
Goto Forum:
  


Current Time: Thu Dec 08 22:10:39 CST 2016

Total time taken to generate the page: 0.17039 seconds