If there is a way to bypass the restriction on creating an INSTEAD OF trigger, for a table with an external key, and ON DELETE CASCADE , if in the trigger, after some manipulations, records will still be deleted? Normal trigger does not offer.

  • Honestly, I did not understand the question. You want to create a trigger, but you do not give? - msi
  • one
    Yes. This is a limitation of MS SQL. INSTEAD triggers for deleting and updating are not allowed to be created on a table that has foreign keys with cascading actions - renegator
  • Thanks, with the translation it became clear. Those. the beginning of the phrase instead of "If the way ..." should be read as "Is there any way ...". :-) - msi

2 answers 2

Not. What is the meaning of ON DELETE in this case?

CREATE TABLE [dbo].[table1]( [id] [int] NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ( [id] ASC ) ) ON [PRIMARY] CREATE TABLE [dbo].[table2]( [i] [int] NOT NULL, [id] [int] NULL, CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ( [i] ASC ) ) ON [PRIMARY] ALTER TABLE [dbo].[table2] WITH CHECK ADD CONSTRAINT [FK_table2_table1] FOREIGN KEY([id]) REFERENCES [dbo].[table1] ([id]) GO ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_table2_table1] GO CREATE TRIGGER [dbo].[td_table1] ON [dbo].[table1] INSTEAD OF DELETE AS DELETE FROM table2 WHERE id IN (SELECT id FROM deleted) DELETE FROM table1 WHERE id IN (SELECT id FROM deleted) RETURN

And now the test data

 INSERT INTO table1 VALUES (1),(2) INSERT INTO table2 VALUES (10,1),(11,1),(12,2)

Remove and see what the trigger executed

 DELETE FROM table1 WHERE id = 1 SELECT * FROM table2 SELECT * FROM table1
  • When deleting records in table A, the records in table B that refer to the deleted in A are deleted in cascade. I want to update it before deleting the record in B. - gcoder
  • Insert the removal into the trigger - renegator
  • That is, all cascading deletes should be moved to table trigger A? - gcoder
  • Of course, since you are replacing the entire delete operation - renegator
  • > that is, all cascading deletes should be moved to table trigger A? not necessarily all. those related to directly dependent tables. - uilenspiegel

It turns out that you do not need ON DELETE CASCADE . Then you have to do referential integrity on triggers.

  • Not. It is not forbidden to create a link, it is enough not to install any actions on it - renegator
  • The meaning of CASCADE is not clear. besides, the default is NO ACTION , which will not allow anything to be removed from A. The INSTEAD OF trigger will roll back. - uilenspiegel
  • Why should he roll back? Test script added to your response - renegator