{"id":2060,"date":"2012-06-01T18:51:25","date_gmt":"2012-06-01T18:51:25","guid":{"rendered":"http:\/\/craftydba.com\/?p=2060"},"modified":"2013-01-23T22:04:28","modified_gmt":"2013-01-23T22:04:28","slug":"various-triggers-part-6","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2060","title":{"rendered":"Various Triggers – Part 6"},"content":{"rendered":"

\"\"<\/a>Triggers can be used to track Data Manipulation Language (DML<\/a>) changes that occur in tables. Today, I am going to expand the AUTOS database script<\/a> 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.<\/p>\n

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.<\/p>\n

Second, I am choosing to have a single table (CREATE TABLE<\/a>) to keep track of the DML changes that are occuring to the database.<\/p>\n

\/*  \r\n\tCreate data level auditing - table.\r\n*\/\r\n\r\n-- Remove table if it exists\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = \r\n    OBJECT_ID(N'[ADT].[LOG_DML_CHANGES]') AND type in (N'U'))\r\nDROP TABLE [ADT].[LOG_DML_CHANGES]\r\nGO\r\n\r\nCREATE TABLE [ADT].[LOG_DML_CHANGES]\r\n(\r\n\t[ChangeId]BIGINT IDENTITY(1,1) NOT NULL,\r\n\t[ChangeDate] [datetime] NOT NULL,\r\n\t[ChangeType] [varchar](20) NOT NULL,\r\n\t[ChangeBy] [nvarchar](256) NOT NULL,\r\n\t[AppName] [nvarchar](128) NOT NULL,\r\n\t[HostName] [nvarchar](128) NOT NULL,\r\n\t[SchemaName] [sysname] NOT NULL,\r\n\t[ObjectName] [sysname] NOT NULL,\r\n\t[XmlRecSet] [xml] NULL,\r\n CONSTRAINT [pk_Ltc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)\r\n) \r\nGO\r\n\r\n-- Add defaults for key information\r\nALTER TABLE [ADT].[LOG_DML_CHANGES] \r\n    ADD CONSTRAINT [df_Ltc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];\r\n\r\nALTER TABLE [ADT].[LOG_DML_CHANGES] \r\n    ADD CONSTRAINT [df_Ltc_ChangeType] DEFAULT ('') FOR [ChangeType];\r\n\r\nALTER TABLE [ADT].[LOG_DML_CHANGES] \r\n    ADD CONSTRAINT [df_Ltc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];\r\n\r\nALTER TABLE [ADT].[LOG_DML_CHANGES] \r\n    ADD CONSTRAINT [df_Ltc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];\r\n\r\nALTER TABLE [ADT].[LOG_DML_CHANGES] \r\n    ADD CONSTRAINT [df_Ltc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
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<\/a>) to make a trigger (CREATE TRIGGER<\/a>) which is called for each INSERT, DELETE and UPDATE action.  <\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
The inserted<\/a> and deleted<\/a> 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.<\/p>\n\n\n\n\n\n\n
TSQL COMMAND<\/td>\nINSERTED TABLE<\/td>\nDELETED TABLE<\/td>\n<\/tr>\n
INSERT<\/td>\nNOT EMPTY<\/td>\nEMPTY<\/td>\n<\/tr>\n
DELETE<\/td>\nEMPTY<\/td>\nNOT EMPTY<\/td>\n<\/tr>\n
UPDATE<\/td>\nNOT EMPTY<\/td>\nNOT EMPTY<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
\/*  \r\n\tCreate data level auditing - triggers.\r\n*\/\r\n\r\n-- Local variables\r\nDECLARE @MYSCH AS sysname;\r\nDECLARE @MYOBJ AS sysname;\r\nDECLARE @MYSQL AS VARCHAR(MAX);\r\nDECLARE @VARSCH AS sysname;\r\nDECLARE @VAROBJ AS sysname;\r\nDECLARE @VARFULL AS sysname;\r\nDECLARE @RET AS VARCHAR(2);\r\n\r\n-- Allocate cursor, return table names\r\nDECLARE MYTRG CURSOR FAST_FORWARD FOR \r\n    SELECT s.name AS SchemaName,  o.name AS ObjectName\r\n    FROM sys.objects o (NOLOCK) JOIN  sys.schemas s (NOLOCK) ON o.[schema_id] = s.[schema_id]\r\n    WHERE o.[type] = 'U' AND s.[name] <> 'ADT'\r\n    ORDER BY s.name, o.name;\r\n\r\n-- Open cursor    \r\nOPEN MYTRG;\r\n\r\n-- Set the cr\/lf\r\nSELECT @RET = CHAR(10) --+ CHAR(13);\r\n\r\n-- Get the first row    \r\nFETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ;\r\n\r\n-- While there is data\r\nWHILE (@@FETCH_STATUS = 0) \r\nBEGIN   \r\n\r\n    -- Set up variables\r\n    SELECT @VARSCH = QuoteName(@MYSCH);\r\n    SELECT @VAROBJ = QuoteName(@MYOBJ);\r\n    SELECT @VARFULL = QuoteName(@MYSCH) + '.' + QuoteName(@MYOBJ);\r\n\r\n    -- Show table name\r\n    PRINT 'Making trigger for ' + @VARFULL;\r\n\r\n    -- Dynamic SQL to drop object\r\n    SELECT @MYSQL = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N' + CHAR(39) \r\n        + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + CHAR(39) + ')) DROP TRIGGER ' \r\n        + @VARSCH + '.' + QUOTENAME('TRG_TRACK_CHGS_' + @MYOBJ) + ';';\r\n    EXEC (@MYSQL);\r\n    --PRINT @MYSQL\r\n    \r\n    -- Dynamic SQL to create object\r\n    SELECT @MYSQL = ''\r\n    SELECT @MYSQL = @MYSQL + 'CREATE TRIGGER ' + @VARSCH + '.' + QUOTENAME('TRG_TRACK_DML_CHGS_' + @MYOBJ) + ' ON ' + @VARFULL + ' ' + @RET\r\n    SELECT @MYSQL = @MYSQL + 'FOR INSERT, UPDATE, DELETE AS ' + @RET + @RET\r\n    SELECT @MYSQL = @MYSQL + '  -- Author:  John Miner ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '  -- Date:  May 2012' + @RET\r\n    SELECT @MYSQL = @MYSQL + '  -- Purpose:  Automated change detection trigger (ins, upd, del).' + @RET + @RET\r\n\r\n    SELECT @MYSQL = @MYSQL + 'BEGIN ' + @RET + @RET\r\n\r\n    SELECT @MYSQL = @MYSQL + '  -- Detect inserts' + @RET\r\n    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '\t  INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Insert'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' \r\n        + CHAR(39) + @VAROBJ + CHAR(39) \r\n        + ', (SELECT * FROM inserted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET\r\n    \r\n    SELECT @MYSQL = @MYSQL + '  -- Detect deletes' + @RET\r\n    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) ' + @RET    \r\n    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Delete'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' \r\n        + CHAR(39) + @VAROBJ + CHAR(39) \r\n        + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET\r\n        \r\n    SELECT @MYSQL = @MYSQL + '  -- Update inserts' + @RET\r\n    SELECT @MYSQL = @MYSQL + '  IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '    BEGIN ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      INSERT [ADT].[LOG_DML_CHANGES] ([ChangeType], [SchemaName], [ObjectName], [XmlRecSet]) ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      SELECT ' + CHAR(39) + 'Update'  + CHAR(39) + ', ' + CHAR(39) + @VARSCH  + CHAR(39) + ', ' \r\n        + CHAR(39) + @VAROBJ + CHAR(39) \r\n        + ', (SELECT * FROM deleted as Record FOR XML AUTO, elements , root(' + CHAR(39) + 'RecordSet' + CHAR(39) + '), type); ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '      RETURN; ' + @RET\r\n    SELECT @MYSQL = @MYSQL + '    END; ' + @RET + @RET\r\n\r\n    SELECT @MYSQL = @MYSQL + 'END;' \r\n\r\n    -- Execute the SQL stmt\r\n    EXEC (@MYSQL);\r\n    --PRINT @MYSQL\r\n    \r\n    -- Get the next row\r\n    FETCH NEXT FROM MYTRG INTO @MYSCH, @MYOBJ;\r\n    \r\nEND;\r\n\r\n-- Close the cursor\r\nCLOSE MYTRG; \r\n\r\n-- Release the cursor\r\nDEALLOCATE MYTRG; \r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Please see image below for triggers that are generated.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[396,317,433,395,431,12,432,15,28,390,29],"class_list":["post-2060","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-after","tag-create-trigger","tag-deleted","tag-dml","tag-exec","tag-free-code","tag-inserted","tag-john-f-miner-iii","tag-sql-server","tag-triggers","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2060","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2060"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2060\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2060"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2060"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2060"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}