SQL Server Trigger Alternatives with the OUTPUT Clause

By: Jeremy Kadlec | Updated: 2007-11-28 | Comments (3) | Related: > Triggers

Problem

I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?

Solution

Auditing data has become more of a need with all of the recent legislation and many internal business needs, so exploring auditing options beyond triggers is probably a great need in many organizations. Here are a few different options to perform the auditing:

Triggers - Triggers are available for any INSERT, UPDATE or DELETE statements performed on a table. The triggers can be setup for specific columns and the data can be written to a separate auditing table.
- Caveat - In your circumstance, the trigger(s) would potentially fire for each INSERT, UPDATE or DELETE statements which sounds like it would be excessive since only auditing is needed in some circumstances. If all of the data is captured, then you would need to delete the unneeded data if the auditing needs to be selective.
Disabling triggers - Triggers can be disabled or enabled based on the need.
- Caveat - If all of the applications run at the same times of the day, then disabling the triggers would potentially lose valuable data.
Stored procedure logic - Logic can be added to select the needed data from the base table or write the input parameters to a table. Based on the business logic standards can be set to perform the data collection.
- Caveat - It will be necessary to change the logic for each stored procedure needed. Although this seems like it is a solution you would prefer since the auditing is per application, the approach may not be as elegant as another approach.
Third party products - A number of third party products are available to meet the auditing needs, but each has a varying level of functionality, flexibility, granularity, etc. and may or may not meet your needs. Check into the products to see which ones make sense.
- Caveat - Depending on the product dictates whether or not the product will be able to selectively capture the needed data.
OUTPUT clause - The OUTPUT clause is new to SQL Server 2005 and has the ability to access the INSERTED and DELETED tables as is the case with a trigger. The OUTPUT command can be added to your stored procedures or T-SQL scripts in order to write the data out to an auditing table or return the data back to the front end client.
- Caveat - It will be necessary to change the logic in your stored procedures and T-SQL scripts to incorporate this new set of functionality.

The OUTPUT Clause

Based on the information provided, the best option to meet your needs appears to be the OUTPUT clause. Logic can be added to specific INSERT, UPDATE or DELETE statements with your existing business logic, so that the audited data can be captured in a separate table. This technique provides a great deal of functionality in order to customize the scope of the auditing to prevent specific applications or conditions from generating auditing data. Here are a few different options when using the OUTPUT clause with tables in the AdventureWorks sample SQL Server 2005 database:

Dependent Audit Table

USE [AdventureWorks]
GO
CREATE TABLE [Person].[Contact_Audit](
[ContactID_Audit] [int] NOT NULL IDENTITY(1,1),
[ContactID] [int] NOT NULL,
[NameStyle] [dbo].[NameStyle] NULL,
[Title] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[Suffix] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
[EmailAddress] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[EmailPromotion] [int] NULL,
[Phone] [dbo].[Phone] NULL,
[PasswordHash] [varchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
[PasswordSalt] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NULL,
[ModifiedDate] [datetime] NULL,
[EnteredDate] [datetime] NULL,
[UserName] [varchar](100),
CONSTRAINT [PK_Contact_ContactID_Audit1] PRIMARY KEY CLUSTERED
(
[ContactID_Audit] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT Statement with an OUTPUT Clause into an Audit Table

OUTPUT INSERTED.ContactID
,INSERTED.[NameStyle]
,INSERTED.[Title]
,INSERTED.[FirstName]
,INSERTED.[MiddleName]
,INSERTED.[LastName]
,INSERTED.[Suffix]
,INSERTED.[EmailAddress]
,INSERTED.[EmailPromotion]
,INSERTED.[Phone]
,INSERTED.[PasswordHash]
,INSERTED.[PasswordSalt]
,INSERTED.[AdditionalContactInfo]
,INSERTED.[rowguid]
,INSERTED.[ModifiedDate]
,NULL
,SUSER_SNAME()

INTO Person.Contact_Audit

VALUES
(0
,'Mr.'
,'MSSQLTips'
,'dot'
,'com'
,'The First'
,'[email protected]'
,1
,555-555-5555
,'Li26cq1s3a+0YJcgjemlepj98r5eUwJlyHGmDJnSCWI='
,'NVfuzjo='
,NULL
,'b50c8dcd-ea86-43a5-af69-84b7a6a7faf3'
,'2007-01-01 00:00:00.000')

-- Verification in the base table
SELECT *
FROM [Person].[Contact]
WHERE [FirstName] = 'MSSQLTips'
GO

-- Verification in the auditing table
SELECT *
FROM Person.Contact_Audit
GO

UPDATE Statement with an OUTPUT Clause into an Audit Table

USE AdventureWorks;
GO
UPDATE [Person].[Contact]
SET [FirstName] = 'The MSSQLTips Team'
OUTPUT INSERTED.ContactID
,INSERTED.[NameStyle]
,INSERTED.[Title]
,INSERTED.[FirstName]
,INSERTED.[MiddleName]
,INSERTED.[LastName]
,INSERTED.[Suffix]
,INSERTED.[EmailAddress]
,INSERTED.[EmailPromotion]
,INSERTED.[Phone]
,INSERTED.[PasswordHash]
,INSERTED.[PasswordSalt]
,INSERTED.[AdditionalContactInfo]
,INSERTED.[rowguid]
,INSERTED.[ModifiedDate]
,NULL
,SUSER_SNAME()
INTO Person.Contact_Audit
WHERE [FirstName] = 'MSSQLTips'
GO

-- Verification in the base table
SELECT *
FROM [Person].[Contact]
WHERE [FirstName] = 'The MSSQLTips Team'
GO

-- Verification in the auditing table
SELECT *
FROM Person.Contact_Audit
GO

DELETE Statement with an OUTPUT Clause into an Audit Table

USE AdventureWorks;
GO
DELETE FROM [Person].[Contact]
OUTPUT DELETED.ContactID
,DELETED.[NameStyle]
,DELETED.[Title]
,DELETED.[FirstName]
,DELETED.[MiddleName]
,DELETED.[LastName]
,DELETED.[Suffix]
,DELETED.[EmailAddress]
,DELETED.[EmailPromotion]
,DELETED.[Phone]
,DELETED.[PasswordHash]
,DELETED.[PasswordSalt]
,DELETED.[AdditionalContactInfo]
,DELETED.[rowguid]
,DELETED.[ModifiedDate]
,NULL
,SUSER_SNAME()
INTO Person.Contact_Audit
WHERE [FirstName] = 'The MSSQLTips Team'
GO

-- Verification in the base table
SELECT *
FROM [Person].[Contact]
WHERE [FirstName] = 'The MSSQLTips Team'
GO

-- Verification in the auditing table
SELECT *
FROM Person.Contact_Audit
GO

INSERT Statement with an OUTPUT Clause into an Audit Table and as a Result Set

USE AdventureWorks;
GO
INSERT INTO [Person].[Contact]
([NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailAddress]
,[EmailPromotion]
,[Phone]
,[PasswordHash]
,[PasswordSalt]
,[AdditionalContactInfo]
,[rowguid]
,[ModifiedDate])
OUTPUT INSERTED.ContactID
,INSERTED.[NameStyle]
,INSERTED.[Title]
,INSERTED.[FirstName]
,INSERTED.[MiddleName]
,INSERTED.[LastName]
,INSERTED.[Suffix]
,INSERTED.[EmailAddress]
,INSERTED.[EmailPromotion]
,INSERTED.[Phone]
,INSERTED.[PasswordHash]
,INSERTED.[PasswordSalt]
,INSERTED.[AdditionalContactInfo]
,INSERTED.[rowguid]
,INSERTED.[ModifiedDate]
,NULL
,SUSER_SNAME()
INTO Person.Contact_Audit
OUTPUT INSERTED.ContactID
,INSERTED.[NameStyle]
,INSERTED.[Title]
,INSERTED.[FirstName]
,INSERTED.[MiddleName]
,INSERTED.[LastName]
,INSERTED.[Suffix]
,INSERTED.[EmailAddress]
,INSERTED.[EmailPromotion]
,INSERTED.[Phone]
,INSERTED.[PasswordHash]
,INSERTED.[PasswordSalt]
,INSERTED.[AdditionalContactInfo]
,INSERTED.[rowguid]
,INSERTED.[ModifiedDate]
,NULL
,SUSER_SNAME()
VALUES
(0
,'Mr.'
,'MSSQLTips'
,'dot'
,'com'
,'The First'
,'[email protected]'
,1
,555-555-5555
,'Li26cq1s3a+0YJcgjemlepj98r5eUwJlyHGmDJnSCWI='
,'NVfuzjo='
,NULL
,'b50c8dcd-ea86-43a5-af69-84b7a6a7fafa'
,'2007-01-01 00:00:00.000')

Verification Steps

-- Verification in the base table
SELECT *
FROM [Person].[Contact]
WHERE [FirstName] = 'MSSQLTips'
GO

-- Verification in the auditing table
SELECT *
FROM Person.Contact_Audit
GO

Next Steps

The OUTPUT clause is a simple set of logic that can be added to existing code to perform auditing without having the need for an all or nothing proposition as is the case with triggers or some other common options.
Before the technology decision is made, the key item to keep in mind with any auditing need are the final reporting requirements. Be sure to understand those requirements, design a technology solution, capture the needed data, build the reporting solution and validate the reports are accurate.
The OUTPUT clause does have some limitations and also has additional functionality such as using with triggers, so check out the entire set of functionality in SQL Server 2005 Books Online OUTPUT Clause (Transact-SQL).
Check out these trigger related tips:
- SQL Server 2005 Exposed - New DDL Auditing Capabilities

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2007-11-28

SQL Server Trigger Alternatives with the OUTPUT Clause

Problem

Solution

Next Steps

About the author

Comments For This Article