SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 deletion in trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

247 Posts

Posted - 10/12/2013 :  19:13:18  Show Profile  Reply with Quote
Hi there,

table1 has a PK_tbl1 which matches FK_tbl1 in table2 and the relationship is one to one. Like this:

table1 has columns of UnitId, Code ...
table2 has columns of ItemID, UnitId ...

So if I need remove an item from table1 I must remove the matched one in table2 first.

delete from table2 where UnitId = xxx
delete from table1 where UnitId = xxx

Now I need create a trigger on table1 for deletion:

CREATE Trigger [dbo].[Table1_Delete]
ON [dbo].[Table1]
FOR DELETE
As
BEGIN
-- Prepare for all the parameters
Declare @UnitId int;
Set @UnitId = (select UnitId from deleted);

Begin Try
begin Tran
Delete from Table2 Where UnitId = @UnitId;
Delete from Table1 Where UnitId = @UnitId;
commit Tran
End Try

Begin Catch
ROLLBACK TRANSACTION;
End Catch

It doesn't work. Somehow I think the way to get @UnitId is not right. Help is appreciated. Thanks in advance.

Edited by - allan8964 on 10/12/2013 19:23:19

allan8964
Posting Yak Master

247 Posts

Posted - 10/12/2013 :  21:33:59  Show Profile  Reply with Quote
Ok, very interesting, I changed FOR DELETE to INSTEAD OF DELETE and keep the rest the same then it works.

Edited by - allan8964 on 10/12/2013 21:36:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2013 :  01:26:49  Show Profile  Reply with Quote
actualy the easiest way to do this is to set FOREIGN KEY by adding ON DELETE CASCADE option in table2. Then deleting a record from table1 will automatically delete the dependent records in table2. Only thing to take care is you should have good supplimentary documentation to explain this otherwise anyone who inherits the code will not know this as the deletion happens on background

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 10/13/2013 01:27:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000