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.
| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2013-10-12 : 19:13:18
|
| 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 = xxxdelete from table1 where UnitId = xxxNow I need create a trigger on table1 for deletion:CREATE Trigger [dbo].[Table1_Delete]ON [dbo].[Table1]FOR DELETEAs BEGIN-- Prepare for all the parametersDeclare @UnitId int; Set @UnitId = (select UnitId from deleted); Begin Trybegin Tran Delete from Table2 Where UnitId = @UnitId; Delete from Table1 Where UnitId = @UnitId;commit Tran End TryBegin Catch ROLLBACK TRANSACTION;End CatchIt doesn't work. Somehow I think the way to get @UnitId is not right. Help is appreciated. Thanks in advance. |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-10-12 : 21:33:59
|
| Ok, very interesting, I changed FOR DELETE to INSTEAD OF DELETE and keep the rest the same then it works. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-13 : 01:26:49
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
deletion in trigger