It explains how to implement an audit trail quickly by creating shadow table for each table in database and triggers to log every time when a record is inserted, updated or deleted in the table.
Steps:
1. First run Scripts By Cedric Baelemans of stored proc which takes table name as argument and create shadow table having some additional fields like AuditId, AuditAction, AuditDate, AuditUser and AuditApp in database.
2. Execute stored proc manually for testing on sample table say Customers.
Exec GenerateAudittrail ‘Customers’,’dbo’
It will create shadow table ‘Customers_shadow‘ with insert, update and delete triggers of Customers. When any record is inserted, updated or deleted, trigger is fired and a record is inserted in shadow table with additional information.
3. We can do this quickly by running it with SSMS tools Pack. It is the best free Sql Server Management Studio(SSMS)’s add-in having lots of features. Install SSMS Tools Pack if not installed and see this post for more details.
4. Click on SSMS Tools > “Run Custom Script” > Options
5. Give Shortcut ‘Audit‘ and Select ‘Table‘ in ‘Enabled On‘ options and enter following sql script.
Exec GenerateAudittrail ‘|ObjectName|’,’|SchemaName|’
6. Click on Save and OK button.
7. Now, Right click on any table > Select “SSMS Tools” > Run Custom Scripts > Audit
That’s it !!!
It will create Audit Table and Triggers automatically. It is very useful and time saver when hundreds of tables in database and we have to implement audit functionality for some tables.
If you are facing issue, see step by step guide to Run Your Custom Script Quickly with SSMS Tools Pack.
Enjoy Auditing !!!
Join was incomplete :
inner join systypes c on b.xtype = c.xtype and b.xusertype = c.xusertype and c.name ‘sysname’
i am gettin followin error while working this method with Adventureworks2008 sample database
exec dbo.GenerateAudittrail ‘shoppingCartItem’, ‘sales’
Creating audit table [sales].[shoppingCartItem_shadow]
Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name ‘ShoppingCartID’ in table ‘shoppingCartItem_shadow’ is specified more than once.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “sales.shoppingCartItem_shadow” because it does not exist or you do not have permissions.
Dropping triggers
Creating triggers
No body like to do audit trail with shadow table. every one like to create single table for all audit trail data.
What is wrong with built-in CDC (Change Data Capture, not available in express edition) and CT (change tracking, available in all editions)?