Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Trigger Alternatives with the OUTPUT Clause


By:   |   Last Updated: 2007-11-28   |   Comments (3)   |   Related Tips: More > 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

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

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:

 



Last Updated: 2007-11-28


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 18, 2012 - 2:07:47 PM - Jeremy Kadlec Back To Top

Matthew,

Depending on the needs, that could be an option.

Thank you,
Jeremy Kadlec


Wednesday, July 18, 2012 - 9:52:56 AM - Matthew Back To Top

What about conditional logic inside of a trigger?

if host_name() <> 'prodserver1'
begin
    insert into table_audit...
end

Monday, May 03, 2010 - 8:17:25 AM - carumuga Back To Top

Thanks. It was a really great to have such an alternative to trigger. However, is there a way where we can capture the BULK INSERT data for auding through OUTPUT clause. Please advise.

 


Learn more about SQL Server tools