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 |
|
companionz
Yak Posting Veteran
54 Posts |
 Posted - 2009-04-30 : 03:12:34
|
| Hi,I have to delete a record from a parent table which has refernces in some child tables. Except 1 of the child tables all have "on Delete cascade" option, now when i delete record from parent table having reference in that 1 table, it is not able to delete record.I can't change the logic of the table to go for on delete cascade.Instead i just want to change the foreign key value in child table to null.So, i want to create a Before delete trigger to resolve this issue.Can anyone help on this?Thanks,Sourav |
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-04-30 : 03:48:15
|
| You cannot achieve it using a trigger. SQL Server will detect the existence of the constraint before executing the trigger. |
 |
|
|
companionz
Yak Posting Veteran
54 Posts |
Posted - 2009-04-30 : 04:27:09
|
| So, how to go about that?Any approach ??? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-30 : 07:12:42
|
| Why can't you change the table?You could get round this by....Write a stored proc to do the delete for you and make it (in a transaction) either set the foreign key to NULL or delete the row first.Then use the sp for deletes.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Before Delete Trigger