| Author |
 Topic  |
|
|
bmassey
Starting Member
USA
22 Posts |
 Posted - 05/30/2007 : 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
|
Edited by - bmassey on 05/30/2007 20:56:44
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/31/2007 : 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
USA
22 Posts |
Posted - 05/31/2007 : 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 - 10/28/2010 : 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
United Kingdom
22191 Posts |
Posted - 10/28/2010 : 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
USA
2 Posts |
Posted - 08/26/2011 : 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
United Kingdom
22191 Posts |
Posted - 08/27/2011 : 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
USA
2 Posts |
Posted - 09/07/2011 : 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. |
Edited by - PwAusSQL on 09/07/2011 19:14:03 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/08/2011 : 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 - 05/23/2012 : 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 Posts |
Posted - 10/18/2012 : 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
|
Edited by - Artttom on 10/18/2012 05:13:33 |
|
|
| |
Topic |
|
|
|
Auto create History Tables and Triggers
