INSTEAD OF DELETE Trigger conflict with ON DELETE CASCADE FK?
In my opinion you do not need an INSTEAD OF trigger. INSTEAD OF trigger work always instead of an operation. You want to throw an exception in some cases - and delete in others So you could use DELETE CASCADE and a normal (AFTER) trigger Inside the trigger you can RAISERROR the exception and probably ROLLBACK your transaction.(There is always an implicit transaction around a trigger).
In my opinion you do not need an INSTEAD OF trigger. INSTEAD OF trigger work always instead of an operation. You want to throw an exception in some cases - and delete in others.So you could use DELETE CASCADE and a normal (AFTER) trigger.
Inside the trigger you can RAISERROR the exception and probably ROLLBACK your transaction. (There is always an implicit transaction around a trigger).
I have on DELETE CASCADE FKs between them so that if you delete a Batch, the associated Bill and BillLine records also get deleted. If you delete a Bill, the associated BillLines get deleted but the Batch record is not affected. Now I need to prevent the delete of a Bill if there is a certain data condition with one or more of the associated BillLine records.
Table Bill clearly needs an INSTEAD OF DELETE trigger. BillId has an ON DELETE CASCADE FK referencing Bill.BillId. It makes sense that I need to make that FK ON DELETE NO ACTION instead, because the INSTEAD OF DELETE trigger effectively replaces the CASCADE functionality.
When I delete a Bill, the INSTEAD OF DELETE will either delete the associated BillLine records or raise an exception depending on certain data conditions. So far, so good. However, because Bill.
BatchId has an ON DELETE CASCADE FK referencing Batch. BatchId, SQL Server will not let me create the trigger. This I do not understand.
Why should I have to build an INSTEAD OF DELETE trigger on Batch just because I have one on Bill? The code to create the tables and keys below (with all extraneous columns and keys omitted) is how things are now, with no ON DELETE CASCADE clauses. The question is, why can't FK_Bill_Batch_BatchId have that clause instead of my having to create an additional INSTEAD OF DELETE trigger?
I don't understand why an ON DELETE CASCADE in this direction should have anything to do with the INSTEAD OF DELETE trigger on the Bill table.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
[Database]Follow up question about [delete(cascade/retrict)]and Update(cascade/restrict)?
Using nhibernate ,to delete entity with details cascade-delete-orphan,why do I have to get the entity with all details, in order to delete them too?
MySQL - MyISAM multi-table delete (similar to ON CASCADE DELETE)?
MySQL Update in Multiple Tables… trigger? cascade?
Delete delete delete delete?
Sql 'delete cascade?