USE [test] GO /****** Object: Trigger [dbo].[nameTriger] Script Date: 07.03.2016 21:09:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[nameTriger] ON [test].[dbo].[name] INSTEAD OF UPDATE AS BEGIN DECLARE @OldName nvarchar(10) DECLARE @oldID INT SET @OldName = (SELECT [name] FROM [test].[dbo].[name] WHERE id = @@IDENTITY); SET @oldID = (SELECT [id] FROM [test].[dbo].[name] WHERE id = @@IDENTITY ); BEGIN INSERT INTO [test].[dbo].[nameHistory] (id, name) VALUES (@oldID, @OldName) END END

It works well, if one row is updated, if several, then it writes only one row to the audit table.

Please tell me what is wrong? Or you can not catch the update more than one line for one update command?

  • What DBMS? Specify in the manual how to specify FOR EACH ROW or FOR EACH STATEMENT (trigger call on each row separately or only on the whole query). Judging by the behavior, you now have FOR EACH STATEMENT. - Fine
  • @ Small judging by the square brackets in the code - this is MS SQL. And judging by Google, there is no separation on row / statement. - Mike
  • msdn.microsoft.com/ru-ru/library/ms190752(v=sql.120).aspx , stackoverflow.com/questions/16625014/… - Mike
  • @Mike, I correctly understood that you need to make a query not from the main tables, but from virtual ones? - one__for__one
  • @one__for__one Yes, apparently, there is at least a table inserted old data probably need to get from the main table by id from inserted - Mike

1 answer 1

If I understood the problem correctly:

 ALTER TRIGGER [dbo].[nameTriger] ON [test].[dbo].[name] INSTEAD OF UPDATE AS BEGIN INSERT INTO [test].[dbo].[nameHistory] (id, name) select id, name from deleted END