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
Go to Top of Page

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.
Go to Top of Page

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!
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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() ?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

fchenaj
Starting Member

3 Posts

Posted - 2013-10-17 : 05:55:54
unspammed
Go to Top of Page

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



Go to Top of Page
   

- Advertisement -