Triggers can be used to track Data Manipulation Language (DML) changes that occur in tables. Today, I am going to expand the AUTOS database script to add table auditing. Auditing is a very good practice when multiple people have rights to make changes to important semi-static data. This is not a good solution if the amount of data is large or number of table changes are huge.
First, I am going to leverage the newly built schema called Audit Database Tracking (ADT) to seperate data tables from audit tables. A key design decision is to have one single table to hold all table changes or have one audit table per data table that is being tracked.
Second, I am choosing to have a single table (CREATE TABLE) to keep track of the DML changes that are occuring to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<span style="color: #008000; font-size:small;">/* Create data level auditing - table. */ -- Remove table if it exists IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ADT].[LOG_DML_CHANGES]') AND type in (N'U')) DROP TABLE [ADT].[LOG_DML_CHANGES] GO CREATE TABLE [ADT].[LOG_DML_CHANGES] ( [ChangeId]BIGINT IDENTITY(1,1) NOT NULL, [ChangeDate] [datetime] NOT NULL, [ChangeType] [varchar](20) NOT NULL, [ChangeBy] [nvarchar](256) NOT NULL, [AppName] [nvarchar](128) NOT NULL, [HostName] [nvarchar](128) NOT NULL, [SchemaName] [sysname] NOT NULL, [ObjectName] [sysname] NOT NULL, [XmlRecSet] [xml] NULL, CONSTRAINT [pk_Ltc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC) ) GO -- Add defaults for key information ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate]; ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeType] DEFAULT ('') FOR [ChangeType]; ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy]; ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName]; ALTER TABLE [ADT].[LOG_DML_CHANGES] ADD CONSTRAINT [df_Ltc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName]; GO </span> |
Third, I am going to create a dynamic TSQL script to create a trigger on each table that I want audited. The code uses a cursor to list all the tables that will be audited. For each table, a dynamic SQL statement is executed (EXEC) to make a trigger (CREATE TRIGGER) which is called for each INSERT, DELETE and UPDATE action.
The resulting data is stored in a xml field for later review. Additional information such as changed by id, change by host and changed datetime are stored in standard columns within the row. If mutliple actions occur against a table during the same transaction, one xml record is generated that contains multiple actions. Please see image below for xml data.
The inserted and deleted system tables are used to capture the in flight data. The code that is generated stores this data into our auditing table. The following table shows which in flight system tables are not empty per TSQL action.
| TSQL COMMAND | INSERTED TABLE | DELETED TABLE |
| INSERT | NOT EMPTY | EMPTY |
| DELETE | EMPTY | NOT EMPTY |
| UPDATE | NOT EMPTY | NOT EMPTY |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
<span style="color: #008000; font-size:small;">/* Create data level auditing - triggers. */ -- Local variables DECLARE @MYSCH AS sysname; DECLARE @MYOBJ AS sysname; DECLARE @MYSQL AS VARCHAR(MAX); DECLARE @VARSCH AS sysname; DECLARE @VAROBJ AS sysname; DECLARE @VARFULL AS sysname; DECLARE @RET AS VARCHAR(2); -- Allocate cursor, return table names DECLARE MYTRG CURSOR FAST_FORWARD FOR SELECT s.name AS SchemaName, o.name AS ObjectName FROM sys.objects o (NOLOCK) JOIN sys.schemas s (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U' AND s.[name] <> 'ADT' ORDER BY s.name, o.name; -- Open cursor OPEN MYTRG; -- Set the cr/lf SELECT @RET = CHAR(10) --+ CHAR(13); -- Get the first row FETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ; -- While there is data WHILE (@@FETCH_STATUS = 0) BEGIN -- Set up variables SELECT @VARSCH = QuoteName(@MYSCH); SELECT @VAROBJ = QuoteName(@MYOBJ); SELECT @VARFULL = QuoteName(@MYSCH) + '.' + QuoteName(@MYOBJ); -- Show table name PRINT 'Making trigger for ' + @VARFULL; -- Dynamic SQL to drop object SELECT @MYSQL = 'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N' + CHAR(39) + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + CHAR(39) + ')) DROP TRIGGER ' + @VARSCH + '.' + QUOTENAME('TRG_TRACK_CHGS_' + @MYOBJ) + ';'; EXEC (@MYSQL); --PRINT @MYSQL -- Dynamic SQL to create object SELECT @MYSQL = '' SELECT @MYSQL = @MYSQL + 'CREATE TRIGGER ' + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + ' ON ' + @VARFULL + ' ' + @RET SELECT @MYSQL = @MYSQL + 'FOR INSERT, UPDATE, DELETE AS ' + @RET + @RET SELECT @MYSQL = @MYSQL + ' -- Author: John Miner ' + @RET SELECT @MYSQL = @MYSQL + ' -- Date: May 2012' + @RET SELECT @MYSQL = @MYSQL + ' -- Purpose: Automated change detection trigger (ins, upd, del).' + @RET + @RET SELECT @MYSQL = @MYSQL + 'BEGIN ' + @RET + @RET SELECT @MYSQL = @MYSQL + ' -- Detect inserts' + @RET SELECT @MYSQL = @MYSQL + ' IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) ' + @RET SELECT @MYSQL = @MYSQL + ' BEGIN ' + @RET SELECT @MYSQL = @MYSQL + ' INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET SELECT @MYSQL = @MYSQL + ' SELECT ' + CHAR(39) + 'Insert' + CHAR(39) + ', ' + CHAR(39) + @VARSCH + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM inserted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET SELECT @MYSQL = @MYSQL + ' RETURN; ' + @RET SELECT @MYSQL = @MYSQL + ' END; ' + @RET + @RET SELECT @MYSQL = @MYSQL + ' -- Detect deletes' + @RET SELECT @MYSQL = @MYSQL + ' IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) ' + @RET SELECT @MYSQL = @MYSQL + ' BEGIN ' + @RET SELECT @MYSQL = @MYSQL + ' INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET SELECT @MYSQL = @MYSQL + ' SELECT ' + CHAR(39) + 'Delete' + CHAR(39) + ', ' + CHAR(39) + @VARSCH + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET SELECT @MYSQL = @MYSQL + ' RETURN; ' + @RET SELECT @MYSQL = @MYSQL + ' END; ' + @RET + @RET SELECT @MYSQL = @MYSQL + ' -- Update inserts' + @RET SELECT @MYSQL = @MYSQL + ' IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) ' + @RET SELECT @MYSQL = @MYSQL + ' BEGIN ' + @RET SELECT @MYSQL = @MYSQL + ' INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET SELECT @MYSQL = @MYSQL + ' SELECT ' + CHAR(39) + 'Update' + CHAR(39) + ', ' + CHAR(39) + @VARSCH + CHAR(39) + ', ' + CHAR(39) + @VAROBJ + CHAR(39) + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET SELECT @MYSQL = @MYSQL + ' RETURN; ' + @RET SELECT @MYSQL = @MYSQL + ' END; ' + @RET + @RET SELECT @MYSQL = @MYSQL + 'END;' -- Execute the SQL stmt EXEC (@MYSQL); --PRINT @MYSQL -- Get the next row FETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ; END; -- Close the cursor CLOSE MYTRG; -- Release the cursor DEALLOCATE MYTRG; GO </span> |
Please see image below for triggers that are generated.
In summary, AFTER triggers can be used to keep track of DATA changes. If the number of DATA changes are small, this is a great solution to find out who did what when there is a production issue. In SQL Server 2008, a built in Change Data Tracking was introduced. We will explore this new feature in future articles.
