There is a trigger that works when 1 record is deleted from the table and the data in another table changes.

How to change the code so that you can delete several records at once? If one entry is deleted from the Books_Readers table, then the book ID must increment the Count of the corresponding entry in the Books table. For example: several readers who were written by 1 author return books to the library.

CREATE TRIGGER Books_up ON [Books_Readers] FOR DELETE AS BEGIN DECLARE @idBook numeric(11, 0) DECLARE @count int; SET @idBook = ( SELECT d.Book_Kod FROM inserted i FULL OUTER JOIN deleted d ON i.Book_Kod = d.Book_Kod) SET @count = (SELECT Count FROM Books WHERE Books.kod = @idBook) update Books SET Count = (Count+1) WHERE kod = @idBook; Print @idBook END GO

Table structure

Denmark code will not work if I execute the query:

 insert INTO Books_Readers(Book_Kod,Reader_Kod,OnHand)VALUES (4,1,'2012-08-08') insert INTO Books_Readers(Book_Kod,Reader_Kod,OnHand)VALUES (4,2,'2012-08-08')

And when I execute this code there will be an error.

 delete Books_Readers WHERE Book_Kod = 4

The subquery returned more than one value. This is prohibited when the subquery follows after =,! =, <, <=,>,> = Or is used as an expression.

  • Give a diagram of your tables. And fill data. Otherwise, it is not clear what should be updated when removing something. - Alexander Petrov

1 answer 1

If I understood correctly, then you should have something like:

 CREATE TRIGGER Books_up ON [Books_Readers] FOR DELETE AS BEGIN set nocount on; with d as ( select Book_Kod, [Count] = count(1) from deleted group by Book_Kod ) update b set b.[Count] = b.[Count] + d.[Count] from Books b join d on d.Book_Kod = b.kod END GO

Those. Group the number of books deleted by code and connect this result with the Books table, updating the quantities in it.

  • You understood correctly) Thank you very much. True, the code for me is a little confusing. But I already like it myself) - x_ror