 |
|
SQL Server Trigger Alternatives with the OUTPUT Clause
|
By: Jeremy Kadlec | Read Comments (1) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More |
|
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 INTO Person.Contact_AuditVALUES ,'Mr.' ,'MSSQLTips' ,'dot' ,'com' ,'The First' ,'tips@mssqltips.com' ,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 FROM [Person].[Contact] WHERE [FirstName] = 'MSSQLTips' GO -- Verification in the auditing table 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 FROM [Person].[Contact] WHERE [FirstName] = 'The MSSQLTips Team' GO -- Verification in the auditing table 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 FROM [Person].[Contact] WHERE [FirstName] = 'The MSSQLTips Team' GO -- Verification in the auditing table 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' ,'tips@mssqltips.com' ,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 -- Verification in the auditing table 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 Update: 11/28/2007
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Monday, May 03, 2010 - 8:17:25 AM - carumuga | Read The Tip |
|
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.
|
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |