WebProfIT Consulting
  • Home
  • Cloud
  • SQL
  • Big Data
  • Contact Us
  • RSS

Categories

  • AWS (1)
  • Big Data (1)
  • Cloud (1)
  • SQL (4)
    • Microsoft SQL Server (3)
  • Tips and Tricks (2)
  • Tutorials (1)

Recent Comments

    • Home
    • Microsoft SQL Server
    • Big Data
    • SQL
    • Using a DDL trigger to control and automate history of data changes

    Using a DDL trigger to control and automate history of data changes

    2016-09-03 Leave a Comment Written by Pavel Danilov
    Time Capsule - Sony tape recorders

    Summary:

    DDL trigger does all the magic of automatic support of historical data, all you need is to run the script against your database (once). After that you can turn history on tables on, off, or pause it. It will keep audit of all schema changes in your database and automatically generate DML triggers on tables. If you change the schema of the table where history enabled, for instance add or remove column, the same change will be applied to the corresponding history table, so you’ll never forget to sync main table with history.

    Application

    Sometimes Business requires to keep a history of data changes in a table, such as when and who updated or removed a record, and what actually has been changed. User interface may take care of it, but sometimes there are alternative channels of data changes, such as Excel spreadsheets linked to the database, or simply direct access to the tables through SQL Management Studio by DBAs and some other users who may even never open user (web) interface. In this case the record of data change may be easily lost.

    Usually in situations like this people use DML triggers which can react on INSERT, DELETE and UPDATE statements and make some additional steps, usually hidden from users, such as moving deleted record to a history table with timestamp etc. For history purpose usually no need to react on INSERT statements, because if record just inserted into the main table, replication of it in history table would be duplication of data. But recording of records removed by DELETE or replaced by UPDATE is considered to be keeping a history.

    In big databases development of DML triggers for every table where Business is interested to have a history on, may be quite an effort.

    Sometimes history is required only temporarily, for instance in such situations like cleanup of not used tables or removal of big chunks of old data. For instance, suppose a table is empty. Is it being used or not? It’s difficult to say because it may be a staging table where data shows up temporarily for some intermediate processing and then always gets removed. When you look at the table, it’s empty, but it doesn’t mean that it’s not needed. Looking at metadata timestamps trying to figure out when the table was accessed last time, rarely helps because almost always there are some system and maintenance processes such as daily backup which access all tables, including empty ones. Turning history on the table on temporary with s simple switch solves the problem: after 2-3 months you look at the history table, and if it’s empty, then nobody did anything to the empty table and it probably can be removed (well, renamed first, and if nobody screams after that for another 2-3 months, them removed).

    Suppose, you’ve found that because of poor database design a few foreign keys were missing and now there are millions of orphan records in some tables. You want to delete them, but it’s a scary operation: what if someone needs to restore something from that garbage? History tables helps: we remove all those millions but keep them in history for a while.

    SQL script below does all the magic of automatic support of historical data, all you need is to run against it your database. After that you can turn history on tables on, off, pause it. It will keep audit of all schema changes in your database and automatically generate DML triggers on tables. If you change schema of a table where the history enabled, for instance add or remove column, the same change will be applied to the corresponding history table, so you’ll never forget to sync main table with history.

    Feel free to try it and let me know what do you think.

    DDL trigger

    Just run this SQL script against the database where history support is required. Trigger attaches itself at the database level and do all magic itself.

    • All Schema changes will be automatically logged in “databaseAuditDDL” table.
    • If extended property “HistoryRequired=Y” attached to a table, automatic history of all data changes will be enabled for that table.
    • If extended property switched to “HistoryRequired=N”, history stops accumulating, but all history accumulated before will be kept
    • If extended property “HistoryRequired” removed from the table, the history gets dropped and all history data accumulated before will be lost (cleaned up)

    You can download the script from Dropbox location, no need to copy/paste it.

    IF EXISTS (SELECT 1 FROM sys.triggers WHERE name='tr_DB_audit_syncHistory' AND parent_class_desc='DATABASE')
    DROP TRIGGER [tr_DB_audit_syncHistory] ON DATABASE
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- Data Definition Trigger begins:
    
    CREATE TRIGGER [tr_DB_audit_syncHistory]
       ON DATABASE
       FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    
    SET NOCOUNT ON
    
    DECLARE @serverName VARCHAR(256)
    DECLARE @databaseName VARCHAR(256)
    DECLARE @eventType VARCHAR(50)
    DECLARE @objectName VARCHAR(256)
    DECLARE @objectType VARCHAR(25)
    DECLARE @sqlcommand VARCHAR(MAX)
    DECLARE @propertyName VARCHAR(256)
    DECLARE @propertyValue VARCHAR(4000)
    DECLARE @loginName VARCHAR(256)
    
    DECLARE @data XML
    SET @data = EVENTDATA()
    
    DECLARE @ip VARCHAR(15)
    SET @ip = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)
    
    SET @serverName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)')
    SET @databaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
    SET @objectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
    SET @objectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
    SET @sqlcommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
    SET @loginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
    SET @propertyName = @data.value('(/EVENT_INSTANCE/PropertyName)[1]', 'varchar(256)')
    SET @propertyValue = @data.value('(/EVENT_INSTANCE/PropertyValue)[1]', 'varchar(256)')
    

    Create schema change log if it doesn’t exist

    IF object_id('databaseAuditDDL') IS NULL
    BEGIN
    
    CREATE TABLE [dbo].[databaseAuditDDL]
       (
       [logId] [int] IDENTITY(1,1) NOT NULL,
       [serverName] [varchar](256) NULL,
       [databaseName] [varchar](256) NULL,
       [eventType] [varchar](50) NULL,
       [objectName] [varchar](256) NULL,
       [objectType] [varchar](25) NULL,
       [sqlCommand] [varchar](max) NULL,
       [propertyName] [varchar](256) NULL,
       [propertyValue] [varchar](4000) NULL,
       [eventDate] [datetime] NOT NULL DEFAULT (getdate()),
       [hostName] [varchar](256) NOT NULL,
       [IPAddress] [varchar](15) NOT NULL,
       [programName] [varchar](256) NOT NULL,
       [loginName] [varchar](256) NOT NULL,
       CONSTRAINT PK_databaseAuditDDL_logId PRIMARY KEY CLUSTERED (logId ASC)
       )
    
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_eventType] ON [dbo].[databaseAuditDDL] ([eventType] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_objectName] ON [dbo].[databaseAuditDDL] ([objectName] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_objectType] ON [dbo].[databaseAuditDDL] ([objectType] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_propertyName] ON [dbo].[databaseAuditDDL] ([propertyName] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_eventDate] ON [dbo].[databaseAuditDDL] ([eventDate] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_hostName] ON [dbo].[databaseAuditDDL] ([hostName] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_IPAddress] ON [dbo].[databaseAuditDDL] ([IPAddress] ASC)
    CREATE NONCLUSTERED INDEX [IX_databaseAuditDDL_loginName] ON [dbo].[databaseAuditDDL] ([loginName] ASC)
    END
    

    Log the change

    INSERT into [databaseAuditDDL]
       (
       serverName,
       databaseName,
       eventType,
       objectName,
       objectType,
       sqlCommand,
       propertyName,
       propertyValue,
       hostName,
       IPAddress,
       programName,
       loginName
       )
    VALUES
       (
       @serverName,
       @databaseName,
       @eventType,
       @objectName,
       @objectType,
       @sqlcommand,
       @propertyName,
       @propertyValue,
       HOST_NAME(),
       @ip,
       PROGRAM_NAME(),
       @loginName
       )
    

    Create common variables for AutoHistory

    DECLARE @q VARCHAR(MAX)
    DECLARE @historyTable VARCHAR(256) = @objectName + 'AutoHistory'
    DECLARE @triggerName VARCHAR(256) = 'tr_UD_' + @objectName + '_logChanges'
    DECLARE @defaultNameAt VARCHAR(256) = 'DF_' + @objectName + '_timestamp'
    DECLARE @defaultNameBy VARCHAR(256) = 'DF_' + @objectName + '_user'
    

    Permanently remove AutoHistory support

    IF (
       @eventType = 'DROP_EXTENDED_PROPERTY' AND
       @propertyName = N'HistoryRequired'
       )
       BEGIN
    
       PRINT '--- Permanently remove AutoHistory support'
    
       IF OBJECT_ID (@triggerName, 'TR') IS NOT NULL
          BEGIN
          SET @q = 'DROP TRIGGER ' + @triggerName
    --    PRINT @q
          EXEC(@q)
          END
    
       IF EXISTS (SELECT object_id FROM sys.tables WHERE name=@historyTable)
          BEGIN
          SET @q = 'DROP TABLE ' + @historyTable
          EXEC (@q)
          END
    
    -- remove technical columns insertedAtAuto and insertedByAuto from main table
    
       SET @q = 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT '+ @defaultNameAt + '; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @objectName + ' DROP COLUMN insertedAtAuto; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT '+ @defaultNameBy + '; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @objectName + ' DROP COLUMN insertedByAuto; ' + CHAR(13)
       EXEC (@q)
    
       END
    

    Temporary pause AutoHistory support

    IF (
       (@eventType = 'CREATE_EXTENDED_PROPERTY' OR @eventType = 'ALTER_EXTENDED_PROPERTY') AND
       @propertyName = N'HistoryRequired' AND
       @propertyValue = N'N'
       )
       BEGIN
       PRINT '--- Temporary pause AutoHistory support'
       IF OBJECT_ID (@triggerName, 'TR') IS NOT NULL
          BEGIN
          SET @q = 'DROP TRIGGER ' + @triggerName
    --    PRINT @q
          EXEC(@q)
          END
       END
    

    Turn on AutoHistory support

    IF (
       (@eventType = 'CREATE_EXTENDED_PROPERTY' OR @eventType = 'ALTER_EXTENDED_PROPERTY') AND
       @propertyName = N'HistoryRequired' AND
       @propertyValue = N'Y'
       )
       BEGIN
    
       PRINT '--- Turn on AutoHistory support'
    
    -- Create AutoHistory table if it doesn't exist
       SET @q = 'BEGIN TRANSACTION; ' + CHAR(13)
       SET @q += 'IF NOT EXISTS (SELECT object_id FROM sys.tables WHERE name=''' + @historyTable +''') ' + CHAR(13)
       SET @q += 'BEGIN ' + CHAR(13)
       SET @q += 'SELECT TOP 0 * INTO ' + @historyTable + ' FROM ' + @objectName + '; ' + CHAR(13)
    
    -- Figure out identity column, if any, and drop IDENTITY property in history table
    
       DECLARE @identityName VARCHAR(100)
       DECLARE @identityDefinition VARCHAR(120)
       SET @identityName = (
          SELECT c.name AS column_name FROM sys.tables AS t
          INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
          WHERE t.name = @objectName AND c.is_identity = 1
          )
    
       IF (@identityName IS NOT NULL)
          BEGIN
          SET @identityDefinition =
            (
            SELECT col.name + ' ' + type.name
            FROM sys.tables AS tab, sys.columns col, sys.types type
            WHERE tab.name = @objectName
            AND tab.OBJECT_ID = col.OBJECT_ID
            AND col.system_type_id = type.system_type_id
            AND col.name = @identityName
            )
          SET @q += 'ALTER TABLE ' + @historyTable + ' DROP COLUMN ' + @identityName + '; ' + CHAR(13)
          SET @q += 'ALTER TABLE ' + @historyTable + ' ADD ' + @identityDefinition + '; ' + CHAR(13)
          END
    
    -- Add required technical columns
    
       SET @defaultNameAt = 'DF_' + @objectName + '_timestamp'
       SET @q += 'ALTER TABLE ' + @objectName + ' ADD insertedAtAuto DATETIME; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT '+ @defaultNameAt + ' DEFAULT GETDATE() FOR insertedAtAuto; ' + CHAR(13)
    
       SET @defaultNameBy = 'DF_' + @objectName + '_user'
       SET @q += 'ALTER TABLE ' + @objectName + ' ADD insertedByAuto VARCHAR(50); ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT '+ @defaultNameBy + ' DEFAULT SYSTEM_USER FOR insertedByAuto; ' + CHAR(13)
    
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD insertedAtAuto DATETIME; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD insertedByAuto VARCHAR(50); ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD historyId INT PRIMARY KEY IDENTITY(1,1); ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD updatedAtAuto DATETIME; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD updatedByAuto VARCHAR(50); ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD deletedAtAuto DATETIME; ' + CHAR(13)
       SET @q += 'ALTER TABLE ' + @historyTable + ' ADD deletedByAuto VARCHAR(50); ' + CHAR(13)
       SET @q += 'END; COMMIT TRANSACTION; ' + CHAR(13)
       EXECUTE (@q)
    
    -- Create DML trigger on the main table
    
       IF OBJECT_ID (@triggerName, 'TR') IS NOT NULL
          BEGIN
          SET @q = 'DROP TRIGGER ' + @triggerName
    --    PRINT @q
          EXEC(@q)
          END
    
       SET @q = 'CREATE TRIGGER ' + @triggerName + ' ON ' + @objectName + ' AFTER UPDATE, DELETE AS ' + CHAR(13)
       SET @q += 'SET NOCOUNT ON; ' + CHAR(13) + CHAR(13)
       SET @q += 'DECLARE @qii VARCHAR(MAX); ' + CHAR(13)
       SET @q += 'DECLARE @qid VARCHAR(MAX); ' + CHAR(13)
       SET @q += 'DECLARE @qsi VARCHAR(MAX); ' + CHAR(13)
       SET @q += 'DECLARE @qsd VARCHAR(MAX); ' + CHAR(13)
       SET @q += 'DECLARE @query VARCHAR(MAX); ' + CHAR(13)
       SET @q += 'DECLARE @tableName VARCHAR(50); ' + CHAR(13)
       SET @q += 'DECLARE @identityName VARCHAR(50); ' + CHAR(13)
       SET @q += 'DECLARE @colName VARCHAR(50); ' + CHAR(13) + CHAR(13)
       SET @q += 'SET @tableName = (SELECT object_name(parent_obj) FROM sysobjects WHERE id = @@PROCID); ' + CHAR(13) + CHAR(13)
    
       SET @q += 'DECLARE COL CURSOR FOR ' + CHAR(13)
       SET @q += 'SELECT c.name AS column_name ' + CHAR(13)
       SET @q += 'FROM sys.tables AS t ' + CHAR(13)
       SET @q += 'INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID ' + CHAR(13)
       SET @q += 'WHERE t.name = @tableName ' + CHAR(13)
       SET @q += 'ORDER BY c.column_id; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'DECLARE @pkcol VARCHAR(100); ' + CHAR(13)
       SET @q += 'DECLARE @pkclause VARCHAR(4000); ' + CHAR(13) + CHAR(13)
    
       SET @q += 'DECLARE PKCOLS CURSOR FOR ' + CHAR(13)
       SET @q += 'SELECT Col.Column_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, ' + CHAR(13)
       SET @q += 'INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ' + CHAR(13)
       SET @q += 'WHERE Col.Constraint_Name = Tab.Constraint_Name ' + CHAR(13)
       SET @q += 'AND Col.Table_Name = Tab.Table_Name ' + CHAR(13)
       SET @q += 'AND Constraint_Type = ''PRIMARY KEY'' ' + CHAR(13)
       SET @q += 'AND Col.Table_Name = ''' + @objectName + '''; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'OPEN PKCOLS; ' + CHAR(13)
       SET @q += 'SET @pkclause = ''1=1'';' + CHAR(13)
       SET @q += 'FETCH NEXT FROM PKCOLS INTO @pkcol; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'WHILE @@FETCH_STATUS=0 ' + CHAR(13)
       SET @q += 'BEGIN ' + CHAR(13)
       SET @q += 'SET @pkclause += '' AND d.'' + @pkcol + '' = '' + ''i.'' + @pkcol ' + CHAR(13)
       SET @q += 'FETCH NEXT FROM PKCOLS INTO @pkcol ' + CHAR(13)
       SET @q += 'END; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'CLOSE PKCOLS; ' + CHAR(13)
       SET @q += 'DEALLOCATE PKCOLS; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'SET @qii = ''INSERT INTO '' + @tableName + ''AutoHistory ( '';' + CHAR(13)
       SET @q += 'SET @qid = ''INSERT INTO '' + @tableName + ''AutoHistory ( '';' + CHAR(13)
       SET @q += 'SET @qsi = ''SELECT '';' + CHAR(13)
       SET @q += 'SET @qsd = ''SELECT '';' + CHAR(13) + CHAR(13)
    
       SET @q += 'OPEN COL; ' + CHAR(13)
       SET @q += 'FETCH NEXT FROM COL INTO @colName; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'WHILE @@FETCH_STATUS = 0 ' + CHAR(13)
       SET @q += 'BEGIN ' + CHAR(13)
       SET @q += 'SET @qii = @qii + @colName + '', '';' + CHAR(13)
       SET @q += 'SET @qid = @qid + @colName + '', '';' + CHAR(13)
       SET @q += 'SET @qsi = @qsi + ''d.''+@colName + '', '';' + CHAR(13)
       SET @q += 'SET @qsd = @qsd + @colName + '', '';' + CHAR(13)
       SET @q += 'FETCH NEXT FROM COL INTO @colName; ' + CHAR(13)
       SET @q += 'END; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'CLOSE COL; ' + CHAR(13)
       SET @q += 'DEALLOCATE COL; ' + CHAR(13) + CHAR(13)
    
       SET @q += 'SET @qii = @qii + '' updatedAtAuto, updatedByAuto'';' + CHAR(13)
       SET @q += 'SET @qsi = @qsi + '' GETDATE(), SYSTEM_USER'';' + CHAR(13)
       SET @q += 'SET @qid = @qid + '' deletedAtAuto, deletedByAuto'';' + CHAR(13)
       SET @q += 'SET @qsd = @qsd + '' GETDATE(), SYSTEM_USER'';' + CHAR(13) + CHAR(13)
    
       SET @q += 'IF object_id(''tempdb.dbo.#inserted'') IS NOT NULL DROP TABLE #inserted; ' + CHAR(13)
       SET @q += 'IF object_id(''tempdb.dbo.#deleted'') IS NOT NULL DROP TABLE #deleted; ' + CHAR(13) + CHAR(13)
    
    -- Unfortunately, tables Inserted and Deleted are not visible in the DDL trigger context and
    -- we need to copy them into temp tables visible wider. Better ideas?
    
       SET @q += 'SELECT * INTO #inserted FROM Inserted; ' + CHAR(13)
       SET @q += 'SELECT * INTO #deleted FROM Deleted; ' + CHAR(13) + CHAR(13)
    
    -- 
    
       SET @q += 'IF EXISTS (SELECT * FROM #inserted) ' + CHAR(13)
       SET @q += 'SET @query = @qii + '') '' + @qsi + '' FROM #deleted d INNER JOIN #inserted i ON '' + @pkclause ' + CHAR(13)
       SET @q += 'ELSE ' + CHAR(13)
       SET @q += 'SET @query = @qid + '') '' + @qsd + '' FROM #deleted'';' + CHAR(13) + CHAR(13)
    
       SET @q += 'EXECUTE (@query); ' + CHAR(13) + CHAR(13)
    
       SET @q += 'DROP TABLE #inserted; ' + CHAR(13)
       SET @q += 'DROP TABLE #deleted; ' + CHAR(13) + CHAR(13)
    
    -- end of DDL trigger code
    
    -- PRINT @q
       EXECUTE (@q)
       END
    

    Synchronize main table schema with AutoHistory

    IF (@objectType = 'TABLE')
    BEGIN
    
    -- Don't do anything with AutoHistory tables to avoid recursive changes
    
       IF (@objectname NOT LIKE '%AutoHistory')
       BEGIN
    
    -- Is auto history on a table on?
    
       DECLARE @historyRequired VARCHAR(1)
       SET @historyRequired = (
          SELECT CONVERT(VARCHAR(1),ep.value)
          FROM sys.extended_properties ep, sys.tables t
          WHERE ep.major_id = t.object_id
          AND ep.minor_id = 0
          AND ep.name = 'HistoryRequired'
          AND t.name = @objectName
          )
    
    -- Autohistory is ON
    
    IF (@historyRequired = 'Y')
       BEGIN
       IF (@eventType = 'ALTER_TABLE' OR @eventType = 'DROP_TABLE')
          BEGIN
    --    Repeat schema changes on history table if the main table changed or dropped
          DECLARE @historySQL VARCHAR(MAX) = REPLACE(@sqlCommand,@objectName,@objectName+'AutoHistory')
          IF (@sqlCommand NOT LIKE '%ADD%CONSTRAINT%' AND @sqlCommand NOT LIKE '%DROP%CONSTRAINT%') EXECUTE (@historySQL)
          END
       END
    END
    
    END
    GO
    

    Finally enable it

    ENABLE TRIGGER [tr_DB_audit_syncHistory] ON DATABASE
    GO
    

    Stored Procedure to Control History

    Stored procedure below controls the trigger. It’s not necessary because you can create extended property on your table by all means: through Microsoft SQL Server Management Studio, or by using standard system procedures such us sp_addextendedproperty. But it’s handy. To turn History on you call

    EXEC usp_AutoHistory @tableName, 'START'
    

    To pause it, without removing accumulated data

    EXEC usp_AutoHistory @tableName, 'PAUSE'
    

    To stop History permanently and to remove all traces of it

    EXEC usp_AutoHistory @tableName, 'STOP'
    

    This is the code of the procedure (you can download it from Dropbox location, no need to copy/paste.

    IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name='usp_AutoHistory') 
       DROP PROCEDURE usp_AutoHistory
    GO
    
    CREATE PROCEDURE [dbo].[usp_AutoHistory]
       @tableName	VARCHAR(100),
       @operation VARCHAR(10)
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @historyRequired VARCHAR(1)
    SET @historyRequired = (
       SELECT CONVERT(VARCHAR(1),ep.value) 
       FROM sys.extended_properties ep, sys.tables t 
       WHERE ep.major_id = t.object_id
       AND ep.minor_id = 0
       AND ep.name = 'HistoryRequired'
       AND t.name = @tableName
       )
    
    IF (UPPER(@operation) = 'START' AND @historyRequired IS NULL)
       BEGIN
       EXEC sys.sp_addextendedproperty @name = N'HistoryRequired', 
          @value = N'Y', @level0type = N'SCHEMA', @level0name = N'dbo', 
          @level1type = N'TABLE',  @level1name = @tableName
       END
    ELSE IF (UPPER(@operation) = 'START' AND @historyRequired != 'Y')
       BEGIN
       EXEC sys.sp_updateextendedproperty @name = N'HistoryRequired', 
          @value = N'Y', @level0type = N'SCHEMA', @level0name = N'dbo', 
          @level1type = N'TABLE',  @level1name = @tableName
       END
    ELSE IF (
       UPPER(@operation) = 'PAUSE' 
       AND (@historyRequired != 'N' OR @historyRequired IS NULL)
       )
       BEGIN 
       IF (@historyRequired IS NULL)
          BEGIN
          EXEC sys.sp_addextendedproperty @name = N'HistoryRequired', 
             @value = N'Y', @level0type = N'SCHEMA', @level0name = N'dbo', 
             @level1type = N'TABLE',  @level1name = @tableName			
          END
          EXEC sys.sp_updateextendedproperty @name = N'HistoryRequired', 
             @value = N'N', @level0type = N'SCHEMA', @level0name = N'dbo', 
             @level1type = N'TABLE',  @level1name = @tableName
       END
    ELSE IF (UPPER(@operation) = 'STOP' AND (@historyRequired IS NOT NULL))
       BEGIN 
       EXEC sys.sp_dropextendedproperty @name = N'HistoryRequired', 
          @level0type = N'SCHEMA', @level0name = N'dbo', 
          @level1type = N'TABLE',  @level1name = @tableName
       END
    ELSE IF (UPPER(@operation) IN ('START', 'PAUSE', 'STOP'))
       PRINT 'Inapplicable operation: ' + @operation
    ELSE 
       PRINT 'Wrong operation: ' + @operation
    END
    GO
    

    Stored Procedures to Recover Data from History

    To restore one table call

    EXEC usp_RestoreFromAutoHistory @tableName, @dateFrom, @dateTo
    

    Download link here

    IF EXISTS (
    	SELECT 1 FROM sys.objects WHERE name='usp_RestoreFromAutoHistory'
    	) 
    DROP PROCEDURE [usp_RestoreFromAutoHistory]
    GO
    
    CREATE PROCEDURE [dbo].[usp_RestoreFromAutoHistory]
    	@table	 	VARCHAR(100),
    	@dateFrom	DATETIME,
    	@dateTo		DATETIME
    AS
    BEGIN
    
    SET NOCOUNT ON
    
    DECLARE @identity BINARY
    DECLARE @col VARCHAR(100)
    DECLARE @q0 VARCHAR(MAX), 
    		@q1 VARCHAR(MAX), 
    		@q2 VARCHAR(MAX), 
    		@q3 VARCHAR(MAX)
    DECLARE @collist VARCHAR(MAX)
    
    SET @collist = ''
    
    DECLARE COLLIST CURSOR FOR
    	SELECT c.name, c.is_identity 
    	FROM sys.tables 
    	AS t INNER JOIN sys.columns c 
    		ON t.OBJECT_ID = c.OBJECT_ID 
    	WHERE t.name = @table 
    	AND c.name NOT IN ('insertedAtAuto',
    		'insertedByAuto','updatedAtAuto','updatedByAuto',
    		'deletedAtAuto','deletedByAuto','historyId')
    	ORDER BY c.is_identity, c.name
    
    OPEN COLLIST
    FETCH NEXT FROM COLLIST INTO @col, @identity
    WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    	SET @collist += @col +','
    	FETCH NEXT FROM COLLIST INTO @col, @identity
    	END
    CLOSE COLLIST
    DEALLOCATE COLLIST
    SET @collist = SUBSTRING(@collist,1,LEN(@collist)-1)
    
    IF (@identity = 1) SET @q0 = 'SET IDENTITY_INSERT ' + 
    	@table + ' ON ' + CHAR(13) ELSE SET @q0 = ''
    
    SET @q1  = 'INSERT INTO ' + @table + 
    	'(' + @collist + ') ' + CHAR(13)
    SET @q2  = ' SELECT ' + @collist 
    SET @q2 += ' FROM ' + @table + 'AutoHistory'
    SET @q2 += ' WHERE deletedAtAuto >= ''' + 
    	CONVERT(VARCHAR,@dateFrom) + ''' AND deletedAtAuto < ''' + 
    	CONVERT(VARCHAR,@dateTo) + '''' + CHAR(13)
    
    IF (@identity = 1) 
    	SET @q3 = 'SET IDENTITY_INSERT ' + @table + ' OFF ' 
    ELSE SET @q3 = ''
    
    EXEC (@q0 + @q1 + @q2 + @q3)
    
    END 
    GO
    

    To restore all tables which have been changed during known period of time call

    EXEC usp_RestoreFromAutoHistory @dateFrom, @dateTo
    

    This procedure simply calls the previous one multiple times. Timestamps for the call can be looked up in databaseAuditDDL table automatically supported by the main DDL trigger.

    
    IF EXISTS (
    	SELECT 1 FROM sys.objects WHERE name='usp_RestoreFromAutoHistoryAllTables'
    	) 
    DROP PROCEDURE [usp_RestoreFromAutoHistoryAllTables]
    GO
    
    CREATE PROCEDURE [dbo].[usp_RestoreFromAutoHistoryAllTables]
    	@dateFrom	DATETIME,
    	@dateTo		DATETIME
    AS
    BEGIN
    
    SET NOCOUNT ON
    
    DECLARE @table VARCHAR(100)
    DECLARE @sqlCommand VARCHAR(4000)
    DECLARE @constraint VARCHAR(4000)
    DECLARE @posFrom INT
    DECLARE @posTo INT
    DECLARE @q VARCHAR(4000)
    
    -- List of tables to restore
    
    DECLARE TABLES CURSOR FOR
    SELECT DISTINCT objectName FROM databaseAuditDDL 
    	WHERE eventType='CREATE_EXTENDED_PROPERTY' 
    	AND propertyName='HistoryRequired' AND propertyValue='Y'
    	AND eventDate >= @dateFrom AND eventDate = @dateFrom 
    		AND eventDate < @dateTo
    		AND objectName = @table 
    		AND sqlCommand LIKE '%WITH CHECK ADD CONSTRAINT%'
    	OPEN CONSTRAINTS
    	FETCH NEXT FROM CONSTRAINTS INTO @sqlCommand
    	WHILE (@@FETCH_STATUS=0)
    		BEGIN
    		SET @posFrom = PATINDEX('%WITH CHECK ADD CONSTRAINT FK%',@sqlCommand)+26
    		SET @posTo = PATINDEX('%FOREIGN KEY%',@sqlCommand)-1
    		SET @constraint = SUBSTRING(@sqlCommand, @posFrom, @posTo-@posFrom)
    		SET @q = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraint
    		PRINT @q
    		EXEC (@q)
    		FETCH NEXT FROM CONSTRAINTS INTO @sqlCommand
    		END
    	CLOSE CONSTRAINTS
    	DEALLOCATE CONSTRAINTS
    	SET @q = 'EXEC usp_RestoreFromAutoHistory ''' + 
    		@table + ''', ''' + CONVERT(VARCHAR,@dateFrom) + 
    		''', ''' + CONVERT(VARCHAR,@dateTo) + ''''
    	PRINT @q
    	EXEC (@q)
    	FETCH NEXT FROM TABLES INTO @table
    	END
    
    CLOSE TABLES
    DEALLOCATE TABLES
    END
    GO
    

    If you find this code useful, please feel free to copy it and adapt for your needs. All suggestions, critics and ideas are very much appreciated.

    More Information

    Microsoft Developer Network: DDL Triggers

    Big Data, Microsoft SQL Server, SQL
    backup, COMMIT, DDL, DDL trigger, DML trigger, Excel, IDENTITY column, INDEX, INSERT, management, Microsoft, Microsoft SQL server, primary key, React, replication, SQL, SQL Server, trigger, XML
    Renaming Primary Keys to Standard

    Leave a Reply Cancel reply

    You must be logged in to post a comment.

    Recent Posts

    • How to shrink size of Linux EBS volume in AWS
    • Data Normalization
    • Renaming Primary Keys to Standard
    • INCLUDE Clause in Non-clustered Indexes
    • Using a DDL trigger to control and automate history of data changes

    Archives

    WebProfIT Consulting, Inc. 2016