Categories

Recent Comments

    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
    , , , , , , , , , , , , , , , , , ,

    Leave a Reply

    Archives