Auto create History Tables and Triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Auto create History Tables and Triggers

Author

Topic

bmassey
Starting Member

22 Posts

Posted - 2007-05-30 : 20:53:45

For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.

We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds.

Here are some details about the script.

The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:

1) Queries system tables to retrieve table schema for @TableName parameter

2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.

3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.


/************************************************************************************************************
Created By:  Bryan Massey
Created On:  3/11/2007
Comments:  Stored proc performs the following actions:
	1) Queries system tables to retrieve table schema for @TableName parameter
	2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include 
           additional history columns.
	3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, 
	   which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
	@TableName VARCHAR(200),
	@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y" 
AS


DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''


DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50) 
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10),  @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)

DECLARE CurHistoryTable CURSOR FOR 

-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription, 
	CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, 
	CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision, 
	CONVERT(VARCHAR(10), SC.XScale) AS FieldScale, 
	CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, 
	@FieldLength, @Precision, @Scale, @AllowNulls

WHILE @@FETCH_STATUS = 0
BEGIN

	-- create list of table columns
	IF LEN(@FieldList) = 0
		BEGIN
			SET @FieldList = @FieldName
			SET @FirstField = @FieldName
		END
	ELSE
		BEGIN
			SET @FieldList = @FieldList + ', ' + @FieldName
		END

	
	IF LEN(@SQLTable) = 0
		BEGIN
			SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
			SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
		END


	SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
	
	IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
		BEGIN
			SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
		END
	ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
		BEGIN
			SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
		END

	
	IF @AllowNulls = 'Y'
		BEGIN
			SET @SQLTable = @SQLTable + ' NULL'
		END
	ELSE
		BEGIN
			SET @SQLTable = @SQLTable + ' NOT NULL'
		END

	SET @SQLTable = @SQLTable + ',' + @CRLF


	FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, 
		@FieldLength, @Precision, @Scale, @AllowNulls
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF 
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF 

SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF 
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF 
SET @SQLTable = @SQLTable + ' )'


PRINT @SQLTable

-- execute sql script to create history table
EXEC(@SQLTable)

IF @@ERROR <> 0
	BEGIN
		PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
		RETURN -1
	END


IF @CreateTrigger = 'Y'
BEGIN
	-- create history trigger
	SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF 
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
	
	
	--PRINT @SQLTrigger
	
	-- execute sql script to create update/delete trigger
	EXEC(@SQLTrigger)

	IF @@ERROR <> 0
		BEGIN
			PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
			RETURN -1
		END

END

Kristen
Test

22859 Posts

Posted - 2007-05-31 : 03:52:12

Nice piece of work

@SQLTrigger and @SQLTable are likely to exceed 8,000 characters, and I expect that the string concatenation is rather slow in SQL server.

We get around this by inserting each additional "line" of the script to a Temp Table, and then SELECTing from that to get the overall script.

FWIW we put the HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction columns FIRST in the Audit table, then as we add more columns to the Main table they are in identical order in the Audit table.

We actually use
INSERT INTO AuditTable SELECT * FROM MainTable
in the trigger so that it breaks if we add an extra column to the Main Table and forget to add it to the Audit table too!

We also prefix our AuditTable column names with "A_" so that in any sort of Join there is less ambiguity between Main table and Audit table columns.

We also tend to set pretty much all columns in the AuditTable to NULL, rather than NOT NULL, so that over time changes to those columns can be reflected in the Audit Table - e.g. a column that was NULL and is now NOT NULL needs to still be NULL in the Audit Table to accommodate the legacy date that was NULL at the time!

Kristen

bmassey
Starting Member

22 Posts

Posted - 2007-05-31 : 07:19:48

Thanks for the feedback Kristen.

Good point about the variables potentially exceeding 8000 characters. I first had this set at 6000 but it blew up on a table that had nearly 200 columns in it. Extending it to 8000 resolved that issue but would still pose problems with tables that contain a very large number of columns, especially if the column names are lengthy.

DeepBlueSea
Starting Member

5 Posts

Posted - 2010-10-28 : 13:02:56

I think it is a great piece of work. It sure helps the first time when the history tables are created. What do you do on an ongoing basis when columns are added or removed from the original tables? Or new tables are added? Do you have a script that run alter scripts?

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 13:46:01

We script EVERYTHING that we change to the database DML. If we ALTER a Column then we add a corresponding ALTER that will adjust the Audit table where necessary. If a column is made narrower we leave the Audit column wider (in case there is any "wide" historical data). If we add a column then we add that also to the Audit table.

PwAusSQL
Starting Member

2 Posts

Posted - 2011-08-26 : 19:46:58

Bryan, your script is great, I tweaked it some using Kristen's suggestions. I've also updated it to be compatible with SQL 2008 with system view/tables. I hope that it helps someone else like yours did for me. I commented out the executes, as we are working in VS2010 and have the DB in a DB project, so I need to create the files for the different schema parts, and I apply the schema parts as I create the files. One could also un-comment the executes and then schema compare to update the schema in the DB Project (something i'm still learning about).

I really liked Kristen's 'select from' to insert to the audit table as I have missed adding a new column to the audittable after adding it to the audited table...

Also please note: we run our audit tables keeping all of the versions of changed rows including inserts. This makes history queries easier, as such the trigger is slightly different.


/************************************************************************************************************
Created By:  Bryan Massey
Created On:  3/11/2007
Comments:  Stored proc performs the following actions:
	1) Queries system tables to retrieve table schema for @TableName parameter
	2) Creates a History table ("Audit" + @TableName) to mimic the original table, plus include 
           additional history columns.
	3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, 
	   which is used to populate the History table.
        4) Writes simple script to pre-populate the Audit table with the current values of the Audited table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
ALTER PROCEDURE AutoGenerateAuditTableAndTrigger
	@TableName VARCHAR(200),
	@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y" 
AS


DECLARE @SQLTable VARCHAR(MAX), @SQLTrigger VARCHAR(MAX), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''


DECLARE @FieldName VARCHAR(100), @DataType VARCHAR(50) 
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10),  @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)

DECLARE CurHistoryTable CURSOR FOR 

-- query system tables to get table schema
SELECT CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, 
	CONVERT(VARCHAR(10),SC.max_length) AS FieldLength, CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision, 
	CONVERT(VARCHAR(10), SC.Scale) AS FieldScale, 
	CASE SC.Is_Nullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM Sys.Objects SO
INNER JOIN Sys.Columns SC ON SO.object_ID = SC.object_ID
INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id
WHERE SO.type = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.Column_Id ASC

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, 
	@FieldLength, @Precision, @Scale, @AllowNulls

WHILE @@FETCH_STATUS = 0
BEGIN

	-- create list of table columns
	IF LEN(@FieldList) = 0
		BEGIN
			SET @FieldList = @FieldName
			SET @FirstField = @FieldName
		END
	ELSE
		BEGIN
			SET @FieldList = @FieldList + ', ' + @FieldName
		END

-- if we are at the start add the std audit columns in front
	IF LEN(@SQLTable) = 0
		BEGIN
			SET @SQLTable = 'CREATE TABLE [DBO].[Audit' + @TableName + '] (' + @CRLF
			SET @SQLTable = @SQLTable + @TAB + '[Audit' + @TableName + 'ID] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
			SET @SQLTable = @SQLTable + @TAB + '[Operation]' + @TAB + 'CHAR (1)        NOT NULL,' + @CRLF
			SET @SQLTable = @SQLTable + @TAB + '[DateOfAction]' + @TAB + 'DATETIME        NOT NULL,' + @CRLF
		END


	SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
	
	IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
		BEGIN
			SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
		END
	ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
		BEGIN
			SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
		END

	
	SET @SQLTable = @SQLTable + ' NULL'
	
	SET @SQLTable = @SQLTable + ',' + @CRLF


	FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, 
		@FieldLength, @Precision, @Scale, @AllowNulls
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

-- finish history table script  and code for Primary key
SET @SQLTable = @SQLTable + ' )' + @CRLF + @CRLF
SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[Audit' + @TableName + ']' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'ADD CONSTRAINT [PK_Audit' + @TableName + 'ID] PRIMARY KEY NONCLUSTERED ([Audit' + @TableName + 'ID] ASC)' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];' + @CRLF + @CRLF

PRINT @SQLTable

-- execute sql script to create history table
--EXEC(@SQLTable)

SET @SQLTrigger = ''

IF @@ERROR <> 0
	BEGIN
		PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
		RETURN -1
	END


IF @CreateTrigger = 'Y'
BEGIN
	-- create history trigger
	SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [' + @TableName + 'AuditUD] ON DBO.' + @TableName + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'AFTER INSERT, DELETE, UPDATE' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF 
	SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'DECLARE @dtNow datetime,' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '        @DCount int,' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '        @ICount int' + @CRLF + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SET NOCOUNT ON;' + @CRLF + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @dtNow = GETDATE()' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @DCount = Count(*) FROM deleted' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @ICount = Count(*) FROM inserted' + @CRLF + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[Audit' + @TableName + ']'+ @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = CASE WHEN @DCount > 0 and @ICount > 0 THEN ''M''' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '            WHEN @ICount > 0 THEN ''A''' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '            ELSE ''D''' + ' END,' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '       [DateOfAction] = @dtNow, I.*' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM   inserted AS I' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '       LEFT OUTER JOIN deleted AS D' + @CRLF
	SET @SQLTrigger = @SQLTrigger + @TAB + '                ON I.' + @TableName + 'ID = D.' + @TableName + 'ID' + @CRLF
	SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF
	
-- and to populate the initial audit entries try this query:
	SET @SQLTrigger = @SQLTrigger + 'INSERT [dbo].[Audit' + @TableName + ']'+ @CRLF
	SET @SQLTrigger = @SQLTrigger + '	SELECT [Operation] = ''A''' + ',' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '		   [DateOfAction] = GETDATE(), T.*' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '   FROM   dbo.' + @TableName + ' AS T' + @CRLF
	
	PRINT @SQLTrigger
	
	-- execute sql script to create update/delete trigger
	--EXEC(@SQLTrigger)

	IF @@ERROR <> 0
		BEGIN
			PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
			RETURN -1
		END

END

Kristen
Test

22859 Posts

Posted - 2011-08-27 : 05:39:09

"we run our audit tables keeping all of the versions of changed rows including inserts. This makes history queries easier, as such the trigger is slightly different."

We don't do that, as we don't want the extra storage cost of storing current-record, but I agree the history queries would be a bit easier.
As we store the previous-version only our history query looks like this (in case helpful to anyone):


SELECT	*
	-- We actually use a full-column list here, instead of SELECT *
FROM
(
	SELECT	[Act] = '*',		-- Indicate Current version (Audit records will show Update or Delete action)
		[Date] = xxx_Updated,	-- Show "audit date" as the latest UpdateDate
		-- Above two columns are present in the Audit Table, included here for UNION
		*
	FROM	dbo.XXX_MyTable
	WHERE	    xxx_PrimaryKey1 = @xxx_PrimaryKey1
		AND xxx_PrimaryKey2 = @xxx_PrimaryKey2
		AND xxx_PrimaryKey3 = @xxx_PrimaryKey3
	UNION ALL
	SELECT	*
	FROM
	(
		SELECT TOP 100 * WITH TIES
		FROM	dbo.XXXa_MyTable
		WHERE	    A_xxx_PrimaryKey1 = @xxx_PrimaryKey1
			AND A_xxx_PrimaryKey2 = @xxx_PrimaryKey2
			AND A_xxx_PrimaryKey3 = @xxx_PrimaryKey3
		ORDER BY A_xxx_AuditDt DESC
	) AS X
) AS X
ORDER BY CASE WHEN [Act] = '*' THEN 1 ELSE 2 END, X.[Date] DESC, xxx_EditNo DESC -- EditNo is a version-number column

"ADD CONSTRAINT [PK_Audit' + @TableName + 'ID] PRIMARY KEY NONCLUSTERED ([Audit' + @TableName + 'ID] ASC)'"

Some reason why this is NON clustered?

We don't create a PK on our audit tables, but we do create a clustered index - using the normal table's PK columns, Audit Date and the EditNo (column containing the version number of the record).
This should be unique, but we don't want two updates in the same millisecond to create a PK violation, so we have it as a (non-unique) clustered index instead so it cannot fail.
We JOIN to the Audit table on PK columns, so PK fields are more useful to us in an index than an ID would be.

I wonder if

"SELECT @DCount = Count(*) FROM deleted
SELECT @ICount = Count(*) FROM inserted"

is rather "expensive"? You are selecting from INSERTED, so @ICount will always be > 0 (and you won't detect a deletion for auditing?)

This perhaps?


-- Added or Modified rows:
INSERT	[dbo].[AuditMyTable]
SELECT	[Operation] = CASE WHEN D.MyPK IS NULL THEN 'A' ELSE 'M' END,
	[DateOfAction] = @dtNow,
	I.*
FROM   inserted AS I
       LEFT OUTER JOIN deleted AS D
                ON D.MyPK = I.MyPK

-- Deleted rows:
INSERT	[dbo].[AuditMyTable]
SELECT	[Operation] = 'D'
	[DateOfAction] = @dtNow,
	I.*
FROM   deleted AS D
       LEFT OUTER JOIN inserted AS I
                ON I.MyPK = D.MyPK
WHERE	I.MyPK IS NULL

Our trigger, given that we are just storing previous-value (not current value), is :


INSERT dbo.AuditMyTable
SELECT	CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END, GetDate(), D.*
FROM	deleted AS D
	LEFT OUTER JOIN inserted AS I
		 ON I.MyPK = D.MyPK

which takes care of both Deleted and Updated states (Insert we ignore, as we have real-table for that value)

PwAusSQL
Starting Member

2 Posts

Posted - 2011-09-07 : 19:03:29

Kristen, excellent suggestions all.

I'll be changing our audit trigger to your suggestion as it's more efficient and simple.

On the PK:
"ADD CONSTRAINT [PK_Audit' + @TableName + 'ID] PRIMARY KEY NONCLUSTERED ([Audit' + @TableName + 'ID] ASC)'"

"Some reason why this is NON clustered?"
I built the script as a generic tool, not to enforce any standard. The NonClustered was to allow for easily adding a clustered key (without having to drop one first) to support what ever queries are built to leverage the Audit table later in the development process. (mumble mumble dance)

"SELECT @DCount = Count(*) FROM deleted
SELECT @ICount = Count(*) FROM inserted"

"is rather "expensive"? You are selecting from INSERTED, so @ICount will always be > 0 (and you won't detect a deletion for auditing?)"
You Bet! This code was a carry over from the original audit triggers designed by a consultant several years ago...

I like this better than my solution:


-- Added or Modified rows:
INSERT	[dbo].[AuditMyTable]
SELECT	[Operation] = CASE WHEN D.MyPK IS NULL THEN 'A' ELSE 'M' END,
	[DateOfAction] = @dtNow,
	I.*
FROM   inserted AS I
       LEFT OUTER JOIN deleted AS D
                ON D.MyPK = I.MyPK

-- Deleted rows:
INSERT	[dbo].[AuditMyTable]
SELECT	[Operation] = 'D',
	[DateOfAction] = @dtNow,
	D.*
FROM   deleted AS D
       LEFT OUTER JOIN inserted AS I
                ON I.MyPK = D.MyPK
WHERE	I.MyPK IS NULL

Thanks for the excellent feedback!
Still learning.

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 02:16:27

On the non-clustered key:

If you have another better clustered key that is made during DEV that is fine of course. Might there be a risk that none is made? If so I think that might impact on the ability to reorganise the table during housekeeping.

Perhaps a "checking script" looking for Audit Tables that have no clustered index that you can run periodically to catch any that have fallen through the net.

This could do with revamping for newer style system tables, but its a start


-- Tables with no PK
SELECT	[Table] = T.name
FROM	sysobjects AS T	-- Table
	LEFT OUTER JOIN sysobjects AS PK	-- PK Name
		 ON PK.parent_obj = T.id
		AND PK.xtype = 'PK' 
WHERE	T.xtype = 'U'
	AND T.name NOT IN ('dtproperties')
	AND PK.name IS NULL	
ORDER BY T.name

hoggy
Starting Member

11 Posts

Posted - 2012-05-23 : 06:26:48

Hi, I know this is an old thread but I am interested in using this script. But I have a problem. I do not use an "ID" column in my tables. the key name varies from table to table and sometimes is a composite key

Is it possible to replace:

SET @SQLTrigger = @SQLTrigger + @TAB + ' ON I.' + @TableName + 'ID = D.' + @TableName + 'ID' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF

with something more generic?
Can we replace ID with ROW_NUMBER() ?

Artttom
Starting Member

1 Post

Posted - 2012-10-18 : 05:09:30

For those who are interested in final code of mentioned solutions


/************************************************************************************************************
Created By:  Bryan Massey
Created On:  3/11/2007
Comments:  Stored proc performs the following actions:
	1) Queries system tables to retrieve table schema for @TableName parameter
	2) Creates a History table ("History" + @TableName) to mimic the original table, plus include 
           additional history columns.
	3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, 
	   which is used to populate the History table.
        4) Writes simple script to pre-populate the History table with the current values of the Historyed table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
18/10/2012 - Artem Tomashevsky - Support of 'max' field length, nvarchar/varchar column types, IDENTITY BACKUP workaround and optimized trigger code
************************************************************************************************************/
ALTER PROCEDURE ForDevelopers_AutoGenerateHistoryTableAndTrigger
    @TableName VARCHAR(200)
,   @CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y" 
AS 
    DECLARE
        @SQLTable VARCHAR(MAX)
    ,   @SQLTrigger VARCHAR(MAX)
    ,   @FieldList VARCHAR(6000)
    ,   @FirstField VARCHAR(200)
    DECLARE
        @TAB CHAR(1)
    ,   @CRLF CHAR(1)
    ,   @SQL VARCHAR(1000)
    ,   @Date VARCHAR(12)

    SET @TAB = CHAR(9)
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
    SET @FieldList = ''
    SET @SQLTable = ''


    DECLARE @PKFieldName VARCHAR(100)
    DECLARE
        @FieldName VARCHAR(100)
    ,   @DataType VARCHAR(50) 
    DECLARE
        @FieldLength VARCHAR(10)
    ,   @Precision VARCHAR(10)
    ,   @Scale VARCHAR(10)
    ,   @FieldDescr VARCHAR(500)
    ,   @AllowNulls VARCHAR(1)
    ,   @FieldIsIdentity BIT
    ,   @FieldLengthInt INT

    DECLARE CurHistoryTable CURSOR
    FOR
        -- query system tables to get table schema
SELECT
        CONVERT(VARCHAR(100), SC.Name) AS FieldName
    ,   CONVERT(VARCHAR(50), ST.Name) AS DataType
    ,   CONVERT(VARCHAR(10), SC.max_length) AS FieldLength
    ,   CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision
    ,   CONVERT(VARCHAR(10), SC.Scale) AS FieldScale
    ,   CASE SC.Is_Nullable
          WHEN 1 THEN 'Y'
          ELSE 'N'
        END AS AllowNulls
    ,   sc.Is_Identity AS FieldIsIdentity
    ,   sc.max_length AS FieldLengthInt
    FROM
        Sys.Objects SO
        INNER JOIN Sys.Columns SC
            ON SO.object_ID = SC.object_ID
-- http://stackoverflow.com/questions/8550427/how-do-i-get-column-type-from-table
-- duplicate column names for Nvarchar fields
-- INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id
        INNER JOIN Sys.Types ST
            ON SC.user_type_id = ST.user_type_id
    WHERE
        SO.type = 'u'
        AND SO.Name = @TableName
    ORDER BY
        SO.[name]
    ,   SC.Column_Id ASC

    OPEN CurHistoryTable

    FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls, @FieldIsIdentity, @FieldLengthInt

    WHILE @@FETCH_STATUS = 0 
        BEGIN

	-- create list of table columns
            IF LEN(@FieldList) = 0 
                BEGIN
                    SET @FieldList = @FieldName
                    SET @FirstField = @FieldName
                END
            ELSE 
                BEGIN
                    SET @FieldList = @FieldList + ', ' + @FieldName
                END

-- if we are at the start add the std History columns in front
            IF LEN(@SQLTable) = 0 
                BEGIN
                    SET @SQLTable = 'CREATE TABLE [DBO].[' + @TableName + '_History] (' + @CRLF
                    SET @SQLTable = @SQLTable + @TAB + '[' + @TableName + '_HistoryID] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
                    SET @SQLTable = @SQLTable + @TAB + '[DateOfAction]' + @TAB + 'DATETIME        NOT NULL  DEFAULT (getdate()),' + @CRLF
                    SET @SQLTable = @SQLTable + @TAB + '[SysUser]' + @TAB + '[nvarchar](30) NOT NULL DEFAULT (suser_sname()),' + @CRLF
-- Application context data. Uncomment and replace with your own function call
--                    SET @SQLTable = @SQLTable + @TAB + '[RlpInitiatorPersonId]' + @TAB + '[int] NULL DEFAULT ([dbo].[fnCurrentPersonID]()),' + @CRLF
                    SET @SQLTable = @SQLTable + @TAB + '[Operation]' + @TAB + 'CHAR (1)        NOT NULL,' + @CRLF
                END


            SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
	
            IF UPPER(@DataType) IN ( 'CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY' ) 
                BEGIN
                    IF @FieldLengthInt = -1 
                        SET @FieldLength = 'MAX'
		
                    SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
                END
            ELSE 
                IF UPPER(@DataType) IN ( 'DECIMAL', 'NUMERIC' ) 
                    BEGIN
                        SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
                    END


	
            SET @SQLTable = @SQLTable + ' NULL'
	
            SET @SQLTable = @SQLTable + ',' + @CRLF


            IF @FieldIsIdentity = 1 
                SET @PKFieldName = @FieldName

            FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls, @FieldIsIdentity, @FieldLengthInt
        END

    CLOSE CurHistoryTable
    DEALLOCATE CurHistoryTable

-- finish history table script  and code for Primary key
    SET @SQLTable = @SQLTable + ' )' + @CRLF + @CRLF
    SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @TableName + '_History]' + @CRLF
    SET @SQLTable = @SQLTable + @TAB + 'ADD CONSTRAINT [PK_' + @TableName + '_HistoryID] PRIMARY KEY CLUSTERED ([' + @TableName + '_HistoryID] ASC)' + @CRLF
    SET @SQLTable = @SQLTable + @TAB
        + 'WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];' + @CRLF
        + @CRLF

    PRINT @SQLTable

-- execute sql script to create history table
    EXEC(@SQLTable)

    SET @SQLTrigger = ''

    IF @@ERROR <> 0 
        BEGIN
            PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
            RETURN -1
        END


    IF @CreateTrigger = 'Y' 
        BEGIN
	-- create history trigger
            SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
            SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [historyTrg_' + @TableName + '] ON DBO.' + @TableName + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'AFTER INSERT, DELETE, UPDATE' + @CRLF
            SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF 
            SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + 'SET NOCOUNT ON;' + @CRLF + @CRLF
	
	-- Here is an IDENTITY BACKUP workaround for @@IDENTITY context corruption problem
	-- Original behavior raise obstacles for after INSERT actions to retrieve new record's identity in a Microsoft way (ADO problem)
	-- http://www.delphigroups.info/2/4/299655.html
	 SET @SQLTrigger = @SQLTrigger + @TAB +'-- START OF IDENTITY BACKUP'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'DECLARE @BackupIdentitySeederFunc VARCHAR(1000)'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'SET @BackupIdentitySeederFunc = '''+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'DECLARE @BackupIdentity TABLE'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'(IdentityID INT IDENTITY('' + CAST(@@IDENTITY AS VARCHAR) + '', 1))'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'INSERT @BackupIdentity DEFAULT VALUES'''+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'-- END OF IDENTITY BACKUP'+ @CRLF



            SET @SQLTrigger = @SQLTrigger + @TAB + '-- Added or Modified rows:' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @TableName + '_History]' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '(Operation, ' + @FieldList + ')'
            SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = CASE WHEN D.' + @PKFieldName + ' IS NULL THEN ''A'' ELSE ''M'' END,' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '       I.*' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM   inserted AS I' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '       LEFT OUTER JOIN deleted AS D' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '                ON I.' + @PKFieldName + '= D.' + @PKFieldName + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '-- Deleted rows:' + @CRLF
	

            SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @TableName + '_History]' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '(Operation, ' + @FieldList + ')'
            SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT	[Operation] = ''D'',' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '       D.*' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM   deleted AS D' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '       LEFT OUTER JOIN inserted AS I' + @CRLF
            SET @SQLTrigger = @SQLTrigger + @TAB + '                ON I.' + @PKFieldName + ' = D.' + @PKFieldName + +@CRLF

SET @SQLTrigger = @SQLTrigger + @TAB +'-- RETRIEVE ORIGINAL IDENTITY'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'EXEC (@BackupIdentitySeederFunc)'+ @CRLF


            SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF
	
-- and to populate the initial History entries try this query:
/*	SET @SQLTrigger = @SQLTrigger + 'INSERT [dbo].['+ @TableName + '_History]'+ @CRLF
	SET @SQLTrigger = @SQLTrigger + '	SELECT [Operation] = ''A''' + ',' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '		   [DateOfAction] = GETDATE(), T.*' + @CRLF
	SET @SQLTrigger = @SQLTrigger + '   FROM   dbo.' + @TableName + ' AS T' + @CRLF
*/	

            
            PRINT @SQLTrigger
	
	-- execute sql script to create update/delete trigger
            EXEC(@SQLTrigger)

            IF @@ERROR <> 0 
                BEGIN
                    PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
                    RETURN -1
                END

        END

jsimone
Starting Member

1 Post

Posted - 2013-09-20 : 13:24:05

Thanks for the script! It is very useful!

I made some updates which I found useful and I thought I'd share them. The main change was a fix to get non-standard column names (i.e. keywords) working by using bracket syntax. Another was adding APP_NAME() in the columns tracked.

/****** Object: StoredProcedure [dbo].[GenerateHistoryTableAndTrigger] Script Date: 09/20/2013 10:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************************************************************
From: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84331
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table (@TableName + "_history") to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
4) Writes simple script to pre-populate the History table with the current values of the Historyed table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
10/18/2012 - Artem Tomashevsky - Support of 'max' field length, nvarchar/varchar column types, IDENTITY BACKUP workaround and optimized trigger code
09/19/2013 - Jason Simone - Added brackets [] to trigger field identifiers to support keyword field names. Added App_Name(). Ignore deletes during updates. Changed insert code to 'I' and update code to 'U'.
************************************************************************************************************/
ALTER PROCEDURE [dbo].[GenerateHistoryTableAndTrigger]
@TableName VARCHAR(200)
, @CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS
DECLARE
@SQLTable VARCHAR(MAX)
, @SQLTrigger VARCHAR(MAX)
, @FieldList VARCHAR(6000)
, @FirstField VARCHAR(200)
DECLARE
@TAB CHAR(1)
, @CRLF CHAR(1)
, @SQL VARCHAR(1000)
, @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''

DECLARE @PKFieldName VARCHAR(100)
DECLARE
@FieldName VARCHAR(100)
, @DataType VARCHAR(50)
DECLARE
@FieldLength VARCHAR(10)
, @Precision VARCHAR(10)
, @Scale VARCHAR(10)
, @FieldDescr VARCHAR(500)
, @AllowNulls VARCHAR(1)
, @FieldIsIdentity BIT
, @FieldLengthInt INT

DECLARE CurHistoryTable CURSOR
FOR
-- query system tables to get table schema
SELECT
CONVERT(VARCHAR(100), SC.Name) AS FieldName
, CONVERT(VARCHAR(50), ST.Name) AS DataType
, CONVERT(VARCHAR(10), SC.max_length) AS FieldLength
, CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision
, CONVERT(VARCHAR(10), SC.Scale) AS FieldScale
, CASE SC.Is_Nullable
WHEN 1 THEN 'Y'
ELSE 'N'
END AS AllowNulls
, sc.Is_Identity AS FieldIsIdentity
, sc.max_length AS FieldLengthInt
FROM
Sys.Objects SO
INNER JOIN Sys.Columns SC
ON SO.object_ID = SC.object_ID
-- http://stackoverflow.com/questions/8550427/how-do-i-get-column-type-from-table
-- duplicate column names for Nvarchar fields
-- INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id
INNER JOIN Sys.Types ST
ON SC.user_type_id = ST.user_type_id
WHERE
SO.type = 'u'
AND SO.Name = @TableName
ORDER BY
SO.[name]
, SC.Column_Id ASC

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls, @FieldIsIdentity, @FieldLengthInt

WHILE @@FETCH_STATUS = 0
BEGIN

-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FirstField = '[' + @FieldName + ']'
SET @FieldList = @FirstField
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', [' + @FieldName + ']'
END

-- if we are at the start add the std History columns in front
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[' + @TableName + '_history] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[' + @TableName + '_historyID] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[DateOfAction]' + @TAB + 'DATETIME NOT NULL DEFAULT (getdate()),' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[SysUser]' + @TAB + '[nvarchar](30) NOT NULL DEFAULT (suser_sname()),' + @CRLF
-- Application context data. Uncomment and replace with your own function call
-- SET @SQLTable = @SQLTable + @TAB + '[RlpInitiatorPersonId]' + @TAB + '[int] NULL DEFAULT ([dbo].[fnCurrentPersonID]()),' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[Application]' + @TAB + '[nvarchar](255) NULL DEFAULT (APP_NAME()),' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[Operation]' + @TAB + 'CHAR (1) NOT NULL,' + @CRLF
END

SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'

IF UPPER(@DataType) IN ( 'CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY' )
BEGIN
IF @FieldLengthInt = -1
SET @FieldLength = 'MAX'

SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE
IF UPPER(@DataType) IN ( 'DECIMAL', 'NUMERIC' )
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END

SET @SQLTable = @SQLTable + ' NULL'

SET @SQLTable = @SQLTable + ',' + @CRLF

IF @FieldIsIdentity = 1
SET @PKFieldName = @FieldName

FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls, @FieldIsIdentity, @FieldLengthInt
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

-- finish history table script and code for Primary key
SET @SQLTable = @SQLTable + ' )' + @CRLF + @CRLF
SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @TableName + '_history]' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'ADD CONSTRAINT [PK_' + @TableName + '_historyID] PRIMARY KEY CLUSTERED ([' + @TableName + '_historyID] ASC)' + @CRLF
SET @SQLTable = @SQLTable + @TAB
+ 'WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];' + @CRLF
+ @CRLF

PRINT @SQLTable

-- execute sql script to create history table
EXEC(@SQLTable)

SET @SQLTrigger = ''

IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END

IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [historyTrg_' + @TableName + '] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AFTER INSERT, DELETE, UPDATE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET NOCOUNT ON;' + @CRLF + @CRLF

-- Here is an IDENTITY BACKUP workaround for @@IDENTITY context corruption problem
-- Original behavior raise obstacles for after INSERT actions to retrieve new record's identity in a Microsoft way (ADO problem)
-- http://www.delphigroups.info/2/4/299655.html
SET @SQLTrigger = @SQLTrigger + @TAB +'-- START OF IDENTITY BACKUP'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'DECLARE @BackupIdentitySeederFunc VARCHAR(1000)'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'SET @BackupIdentitySeederFunc = '''+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'DECLARE @BackupIdentity TABLE'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'(IdentityID INT IDENTITY('' + CAST(@@IDENTITY AS VARCHAR) + '', 1))'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'INSERT @BackupIdentity DEFAULT VALUES'''+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'-- END OF IDENTITY BACKUP'+ @CRLF

SET @SQLTrigger = @SQLTrigger + @TAB + '-- Deleted rows:' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @TableName + '_history]' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(Operation, ' + @FieldList + ')'
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = ''D'',' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' D.*' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM deleted AS D' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'LEFT OUTER JOIN inserted AS I ON I.' + @PKFieldName + ' = D.' + @PKFieldName + +@CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'WHERE I.' + @PKFieldName + ' IS NULL' + @CRLF

SET @SQLTrigger = @SQLTrigger + @TAB + '-- Added or Modified rows:' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @TableName + '_history]' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(Operation, ' + @FieldList + ')'
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = CASE WHEN D.' + @PKFieldName + ' IS NULL THEN ''I'' ELSE ''U'' END,' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' I.*' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM inserted AS I' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'LEFT OUTER JOIN deleted AS D ON I.' + @PKFieldName + '= D.' + @PKFieldName + @CRLF

SET @SQLTrigger = @SQLTrigger + @TAB +'-- RETRIEVE ORIGINAL IDENTITY'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB +'EXEC (@BackupIdentitySeederFunc)'+ @CRLF

SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF

-- and to populate the initial History entries try this query:
/* SET @SQLTrigger = @SQLTrigger + 'INSERT [dbo].['+ @TableName + '_history]'+ @CRLF
SET @SQLTrigger = @SQLTrigger + ' SELECT [Operation] = ''A''' + ',' + @CRLF
SET @SQLTrigger = @SQLTrigger + ' [DateOfAction] = GETDATE(), T.*' + @CRLF
SET @SQLTrigger = @SQLTrigger + ' FROM dbo.' + @TableName + ' AS T' + @CRLF
*/

PRINT @SQLTrigger

-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)

IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END

END

fchenaj
Starting Member

3 Posts

Posted - 2013-10-17 : 05:55:54

unspammed

Geraldziu
Starting Member

1 Post

Posted - 2013-11-18 : 09:39:45

Hello,

I wanted to test this solution and I was able to, but for some tables I have a problem.

In addition, if ktosc could give me some advices how to convert the script to take into account the pattern in which there is a table and made a copy of the table in the same schema. Is it enough to replace in a few places schema 'dbo' and substituted for variable there?

below is the script to create the table at which I had a problem


USE [Analiza]
GO

/****** Object:  Table [Prognozy].[DE]    Script Date: 11/18/2013 15:03:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Prognozy].[DE](
	[nr_katalogowy] [nvarchar](50) NULL,
	[01] [int] NULL,
	[02] [int] NULL,
	[03] [int] NULL,
	[04] [int] NULL,
	[05] [int] NULL,
	[06] [int] NULL,
	[07] [int] NULL,
	[08] [int] NULL,
	[09] [int] NULL,
	[10] [int] NULL,
	[11] [int] NULL,
	[12] [int] NULL,
	[Wprowadzajacy] [nvarchar](50) NULL,
	[data_wprowadzenia] [datetime] NULL,
	[modyfikujacy] [nvarchar](50) NULL,
	[data_modyfikacji] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [Prognozy].[DE] ADD  CONSTRAINT [DF_DE_wprowadzajacy]  DEFAULT (suser_sname()) FOR [Wprowadzajacy]
GO

ALTER TABLE [Prognozy].[DE] ADD  CONSTRAINT [DF_DE_data_wprowadzenia]  DEFAULT (getdate()) FOR [data_wprowadzenia]
GO

ALTER TABLE [Prognozy].[DE] ADD  CONSTRAINT [DF_DE_modyfikujacy]  DEFAULT (suser_sname()) FOR [modyfikujacy]
GO

ALTER TABLE [Prognozy].[DE] ADD  CONSTRAINT [DF_DE_data_modyfikacji]  DEFAULT (getdate()) FOR [data_modyfikacji]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources