Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to delete this in a trigger?

Author  Topic 

allan8964
Posting Yak Master

249 Posts

Posted - 2013-04-03 : 23:01:53
Hi there,

I have table1 with column Code which is related to table2 as PK-FK relations. So if I need delete a row in table1 I need delete all FK-ed rows in table2 first. How can I do this in a trigger? If I use

Create Trigger Code_Delete
On table1
After Delete


but this is supposed to start work after items in table1 is successfully removed, right? But I need remove all the related rows intab2 first. Plz guide me out of this. Thanks in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 00:20:19
Create Trigger Code_Delete
On table1
INSTEAD OF DELTE
AS
BEGIN
--Your sql code to delete data from child tables and then from parent
END
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 2013-04-04 : 00:29:30
Thanks bandi. But I am no sure how to write the code here.
If I want to delete 'ABC' from table1 and I need know ID of 'ABC' and then remove all rows in table2 with same ID. But how can I get ID here? Plz show me more details. Thanks again.
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 2013-04-04 : 00:36:38
Hi Bandi, I got it got ID from DELETED! Ha, Thank you!
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 00:39:57
quote:
Originally posted by allan8964

Hi Bandi, I got it got ID from DELETED! Ha, Thank you!


Welcome

Note: Refer the link to get knowledge on TRIGGERS
http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 00:44:36
Alternative solution is to use ON DELETE CASCADE option for the integrity maintenance of data
Go to Top of Page
   

- Advertisement -