AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Delete duplicate rows in sql3/31/2023 ![]() I don't know if it has to do with a update in MS SQL, but it seems that you do not require 3 triggers to fix this issue on the latest versions. Thursday, Decem2:49:16 AM - Kasper Cottaar REFERENCES dbo.GrandChild (GrandChildID) PRIMARY KEY CLUSTERED (GrandGrandChildID ASC)ĪLTER TABLE dbo.GrandGrandChild WITH CHECKĪDD CONSTRAINT FK_GrandGrandChild_GrandChild The above code doesn't fit the below schema. Thursday, Decem12:52:53 PM - IssacEinstein Please take note of the fact that because we are using an INSTEAD OF trigger we also have to remove the records from the parent table itself after the records have been removed from the child tables. Now that we no longer have any foreign keys with the DELETE CASCADE option set we can add INSTEAD OF triggers to each table to handle the deletion of the child records. recreate all foreign keys without DELETE CASCADE option ![]() drop constraints with DELETE CASCADE optionĪLTER TABLE. We can use the following code to remove the DELETE CASCADE option from all of the foreign key constraints. Although this approach would work, I think a better solution is to remove the DELETE CASCADE option from all the foreign keys and simply use INSTEAD OF triggers on all of the tables to handle removing the child records. I like to keep things consistent so having DELETE CASCADE on some of the tables foreign keys and using INSTEAD OF triggers on other tables becomes quite confusing and difficult to manage. To get around this restriction we have to remove the DELETE CASCADE option from the foreign key on the Child2 table before we can add the DELETE CASCADE option to the GrandChild tables foreign key and then add an INSTEAD OF trigger to the Parent table. Is because the table has a FOREIGN KEY with cascading DELETE or UPDATE. Msg 2113, Level 16, State 1, Procedure DELETE_Child2, Line 8Ĭannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'DELETE_Child2' on table 'dbo.Child2'. WITH CHECKĪfter executing the code above we get the following error and confirm the initial assumption we made after looking at the database diagram, that we have multiple paths down to the GrandChild table. CONSTRAINT PRIMARY KEY CLUSTEREDĪDD CONSTRAINT FOREIGN KEY()ĪLTER TABLE.
0 Comments
Read More
Leave a Reply. |