Our dba and I had a short discussion on triggers, because I could not locate an answer to this question:<\/p>\n
When inserting, updating or deleting more than one row in a table, is the trigger fired iteratively for each affected row, or once with all the records in the inserted and deleted tables?<\/p><\/blockquote>\n
To me it seemed logical that multiple rows could appear in these tables, yet I had heard from any number of people that the calls were iterative. The answer was important, since I was writing triggers to populate an audit table, and I needed to account for single rows or multiple rows in the logic.<\/p>\n
So with the differing opinions, and not being able to get a definitive written answer, I proceeded to write a test in SQL Server 2005 Express to test the behavior. The test was easy:<\/p>\n
First, I created a simple table called primary.<\/p>\n
\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_PADDING ON\r\nGO\r\nCREATE TABLE [dbo].[Primary](\r\n[ID] [int] IDENTITY(1,1) NOT NULL,\r\n[Name_Value] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n[Updated] [datetime] NOT NULL CONSTRAINT [DF_Primary_Updated] DEFAULT (getdate()),\r\nCONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED\r\n(\r\n[ID] ASC\r\n) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\nGO\r\nSET ANSI_PADDING OFF\r\n<\/pre>\nNext, I created Primary_AUDIT, which wrapped extra administrative columns around the original table<\/p>\n
\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_PADDING ON\r\nGO\r\nCREATE TABLE [dbo].[Primary_AUDIT](\r\n [Primary_AUDIT_ID] [bigint] IDENTITY(1,1) NOT NULL,\r\n [ID] [int] NOT NULL, \r\n [Name_Value] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n [Updated] [datetime] NOT NULL CONSTRAINT [DF_Primary_AUDIT_Updated] DEFAULT (getdate()),\r\n [AUDIT_TYPE] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n [AUDIT_DATE] [datetime] NOT NULL,\r\n [AUDIT_GUID] [uniqueidentifier] NOT NULL,\r\nCONSTRAINT [PK_Primary_AUDIT] PRIMARY KEY CLUSTERED\r\n(\r\n [Primary_AUDIT_ID] ASC\r\n) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\nGO\r\nSET ANSI_PADDING OFF\r\n<\/pre>\nThe AUDIT_GUID column is important for this test. Here is the trigger I wrote for inserts (note that it uses FOR INSERT, but AFTER INSERT also has the same behavior).<\/p>\n
\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nCREATE TRIGGER [dbo].[tr_Primary_i]\r\nON [dbo].[Primary]\r\nFOR INSERT\r\n\r\nAS\r\n\r\nBEGIN\r\nSET NOCOUNT ON;\r\n\r\nDECLARE @dNow DATETIME\r\nSET @dNow = GETDATE()\r\n\r\nDECLARE @xGUID UNIQUEIDENTIFIER\r\nSET @xGUID = NEWID()\r\n\r\nINSERT INTO Primary_AUDIT (\r\n ID,\r\n Name_Value,\r\n Updated,\r\n AUDIT_TYPE,\r\n AUDIT_DATE,\r\n AUDIT_GUID)\r\n SELECT\r\n ID,\r\n Name_Value,\r\n Updated,\r\n 'INSERTED',\r\n @dNow,\r\n @xGUID\r\nFROM Inserted\r\n\r\nEND\r\n<\/pre>\nNote that both the AUDIT_DATE and AUDIT_GUID are populated with local variables, set before the insert statement, to ensure that every row is updated with the same datetime in AUDIT_DATE, and the same uniqueidentifier in AUDIT_GUID.<\/p>\n
So here is the acid test. I load a memory table with 5 rows, and the inject the rows in table Primary using a select statement. This inserts 5 rows.<\/p>\n
\r\nDECLARE @p TABLE (Name_Value varchar(500))\r\n\r\nINSERT INTO @p (Name_Value) VALUES ('Test0')\r\nINSERT INTO @p (Name_Value) VALUES ('Test1')\r\nINSERT INTO @p (Name_Value) VALUES ('Test2')\r\nINSERT INTO @p (Name_Value) VALUES ('Test3')\r\nINSERT INTO @p (Name_Value) VALUES ('Test4')\r\n\r\nINSERT INTO [Primary] (Name_Value)\r\nSELECT Name_Value FROM @p\r\n<\/pre>\nWhen executed, this code will show five iterations of “1 row(s) affected”, then a single iteration of “5 row(s) affected”. That last line is the first red flag that inserted has more than one row. To verify what was inserted:<\/p>\n
\r\nSELECT * FROM [Primary]\r\n\r\nSELECT * FROM [Primary_AUDIT]\r\n<\/pre>\nNotice that in the AUDIT table, all the unique identifiers are the same. So the inserted table does have more than one row.<\/p>\n
Keep this behavior in mind when writing triggers, especially when a trigger needs to write multiple lines for a single row (e.g. OLD and NEW rows for an update) and the information needs some kind of tag to associate the records.<\/p>\n","protected":false},"excerpt":{"rendered":"
Our dba and I had a short discussion on triggers, because I could not locate an answer to this question: When inserting, updating or deleting more than one row in a table, is the trigger fired iteratively for each affected row, or once with all the records in the inserted and deleted tables? To me […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-11","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/11","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11"}],"version-history":[{"count":1,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":416,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/11\/revisions\/416"}],"wp:attachment":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}