Just a simple little post about a conversation about triggers I had earlier today with a developer, specifically using them to audit update and delete statements against a table. During our talk I mentioned that, starting in SQL 2005, the inserted and deleted tables were available outside of a trigger. He wasn’t aware of that until I mentioned it. So, for anyone else who’s not familiar with the inserted and deleted tables, here’s a little summary.
Briefly, when you insert, update, or delete records from a table, SQL stores those records in a virtual table; inserted for inserts and deleted for updates and deletes. These tables are available inside of table triggers. Microsoft SQL Books Online has an article explaining the use of the inserted and deleted tables inside of triggers; read that first as a refresher. In our shop we use them for monitoring who is updating and deleting certain critical tables, and we can roll back changes if necessary.
Starting with SQL 2005, you can use the OUTPUT clause to access the inserted and deleted tables outside of DML triggers. You can read the article in Books On Line.
Here’s a simple demo script to run. I added a primary key and a non nullable column to show what happens in error conditions.
USE tempdb;
GO
SET NOCOUNT ON;
GO
-- Create a testing table
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestOutput]') AND type in (N'U'))
DROP TABLE [dbo].[TestOutput]
GO
USE [tempdb]
GO
CREATE TABLE [dbo].[TestOutput](
[Field1] [int] NOT NULL,
[Field2] [char](1) NOT NULL,
CONSTRAINT [PK_TestOutput] PRIMARY KEY CLUSTERED
(
[Field1] ASC
)ON [PRIMARY]
) ON [PRIMARY]
GO
--Testing of Outupt statement
DECLARE @TestOutput_Audit TABLE
(
Field1 INT,
Field2 CHAR(1),
[Action] CHAR(1),
Action_by VARCHAR(25),
Action_Date DATETIME
)
-- Testing Insert
INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')
INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')
SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit
-- Testing Update
UPDATE TestOutput
SET Field2 = 'B'
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1
UPDATE TestOutput
SET Field2 = NULL
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1
SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit
-- Testing Delete
DELETE TestOutput
OUTPUT deleted.Field1, deleted.Field2, 'D', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1
SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit
The INSERT statement simply inserts a single record into my table. Afterwards you can see the same record in the table as well as my audit table. The second record wasn’t added to either table because it’s a primary key violation.
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK_TestOutput'. Cannot insert duplicate key in object 'dbo.TestOutput'.
The statement has been terminated.
Field1 Field2
----------- ------
1 A
Field1 Field2 Action Action_by Action_Date
----------- ------ ------ ------------------------- -----------------------
1 A I PERTELLT3\John 2010-03-24 17:58:03.407
I make a simple UPDATE. Now the table shows the update and the audit shows the value before the update took place. The second update fails because Field2 doesn’t allow NULL values; the main record isn’t updated nor added to the audit table.
Msg 515, Level 16, State 2, Line 33
Cannot insert the value NULL into column 'Field2', table 'tempdb.dbo.TestOutput'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Field1 Field2
----------- ------
1 B
Field1 Field2 Action Action_by Action_Date
----------- ------ ------ ------------------------- -----------------------
1 A I PERTELLT3\John 2010-03-24 17:58:03.407
1 A U PERTELLT3\John 2010-03-24 17:58:03.413
Finally, when I delete the record I can see the record is gone from the main table but stored in the audit table.
Field1 Field2
----------- ------
Field1 Field2 Action Action_by Action_Date
----------- ------ ------ ------------------------- -----------------------
1 A I PERTELLT3\John 2010-03-24 17:58:03.407
1 A U PERTELLT3\John 2010-03-24 17:58:03.413
1 B D PERTELLT3\John 2010-03-24 17:58:03.413
