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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Posting Yak Master

249 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]
-- 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
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

Posting Yak Master

249 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

Very Important crosS Applying yaK Herder

52326 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

Edited by - visakh16 on 10/13/2013 01:27:09
Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000