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 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.

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:

First, I created a simple table called primary.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRIMARY](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Name_Value] [VARCHAR](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Updated] [datetime] NOT NULL CONSTRAINT [DF_Primary_Updated] DEFAULT (getdate()),
CONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Next, I created Primary_AUDIT, which wrapped extra administrative columns around the original table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Primary_AUDIT](
  [Primary_AUDIT_ID] [BIGINT] IDENTITY(1,1) NOT NULL,
  [ID] [INT] NOT NULL, 
  [Name_Value] [VARCHAR](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Updated] [datetime] NOT NULL CONSTRAINT [DF_Primary_AUDIT_Updated]  DEFAULT (getdate()),
  [AUDIT_TYPE] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [AUDIT_DATE] [datetime] NOT NULL,
  [AUDIT_GUID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Primary_AUDIT] PRIMARY KEY CLUSTERED
(
  [Primary_AUDIT_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

The 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).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[tr_Primary_i]
ON  [dbo].[PRIMARY]
FOR INSERT
 
AS
 
BEGIN
SET NOCOUNT ON;
 
DECLARE @dNow DATETIME
SET @dNow = GETDATE()
 
DECLARE @xGUID UNIQUEIDENTIFIER
SET @xGUID = NEWID()
 
INSERT INTO Primary_AUDIT (
  ID,
  Name_Value,
  Updated,
  AUDIT_TYPE,
  AUDIT_DATE,
  AUDIT_GUID)
  SELECT
  ID,
  Name_Value,
  Updated,
  'INSERTED',
  @dNow,
  @xGUID
FROM Inserted
 
END

Note 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.

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.

1
2
3
4
5
6
7
8
9
10

DECLARE @p TABLE (Name_Value VARCHAR(500))
 
INSERT INTO @p (Name_Value) VALUES ('Test0')
INSERT INTO @p (Name_Value) VALUES ('Test1')
INSERT INTO @p (Name_Value) VALUES ('Test2')
INSERT INTO @p (Name_Value) VALUES ('Test3')
INSERT INTO @p (Name_Value) VALUES ('Test4')
 
INSERT INTO [PRIMARY] (Name_Value)
SELECT Name_Value FROM @p

When 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:

1
2
3

SELECT * FROM [PRIMARY]
 
SELECT * FROM [Primary_AUDIT]

Notice that in the AUDIT table, all the unique identifiers are the same. So the inserted table does have more than one row.

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.

No Comments
SQL Server