DELETE Authors FROM Authors INNER JOIN Books_Authors ON Authors.kod=Books_Authors.Author_Kod INNER JOIN Books ON Books_Authors.Book_Kod=Books.kod WHERE Books.Count = 0

There is a code that deletes all authors, the number of books in which is equal to 0. How to alter the code to delete with the help of the mssql trigger?

Here are my attempts:

 alter trigger deleteBooks ON Authors FOR DELETE AS DELETE Authors from Authors INNER JOIN Books_Authors ON Authors.kod=Books_Authors.Author_Kod INNER JOIN Books on Books_Authors.Book_Kod=Books.kod WHERE Books.Count = 0 GO
  • And why do it at all when removing from the table of authors. If it were possible, then the trigger would get stuck in your hands, when deleting a single entry of authors, a trigger will be triggered, which will start deleting authors, but again a trigger for deleting authors will be triggered and so on - Mike
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

 CREATE TRIGGER TR_ZeroBookDelete ON Books AFTER UPDATE AS BEGIN DELETE Authors WHERE Kod IN (SELECT Authors_Kod FROM Books_Authors WHERE Book_Kod IN (SELECT Kod FROM INSERTED WHERE Count = 0)) END GO

This is just an analogue of your first request in the form of a trigger. Some moments he does not solve, say, a violation of data integrity. With this deletion, you will not have the corresponding record in the Authors table for Authors_Kod in the Books_Authors table. The language does not turn to call this field a foreign key, since in the case of a foreign key your request would not work because of an attempt to violate the integrity of the data. It is also not clear what will happen if someone later changes the Count field in the Books table to a value greater than zero: The author has already been deleted, but there are books again.