|
Great Job! I added one modification to fit into my project, this change is handling the case the source table change (like when upgrading software) it is updating the schema instead of deleting the table. This way we keep the records, if any field no longer exists it remains in the audit table but is excluded from the listOfFields so triggers will ignore it.
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table already exists. Will update table schema with new fields.'
DECLARE @ExcludeColumnNames VARCHAR(2000),
@NewFields VARCHAR(2000) = '',
@ExistingFields VARCHAR(2000)
SET @ExcludeColumnNames = 'AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp'
SELECT @ExistingFields = COALESCE(@ExistingFields + ',', '') + sc.name
FROM sysobjects so
INNER JOIN syscolumns sc on so.id = sc.id
WHERE so.id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND CHARINDEX(sc.name, @ExcludeColumnNames) = 0
ORDER BY sc.colId
SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @SourceTableAlias + '.' + @ColumnName + ','
... More statements here
END
END
SET @CreateStatement = LEFT(@CreateStatement, DATALENGTH(@CreateStatement) -1)
IF(LEN(@NewFields) > 0)
BEGIN
PRINT 'Adding new Fields to audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
PRINT @CreateStatement
EXEC (@CreateStatement)
END
END
|
|
|
|
|
Do you have the full script? Because the snippet is incomplete, missing variables, missing parts. I think you pasted a wrong/old copy of the script you had...
|
|
|
|
|
Instead of suser_sname() I'd like to capture the hostname of the client. However, Host_Name() only seems to capture the hostname of the server itself.
|
|
|
|
|
I receive the follow error when attempting to create a _shadow table on certain tables. From trying to troubleshoot I think this is because of the size of the generated SP? Any help/tip appreciated. If there's specific info I can post to help please let me know.
Msg 2717, Level 16, State 2, Line 1
The size (8000) given to the parameter 'Summary' exceeds the maximum allowed (4000).
Best Regards,
Chris
Update: I changed the EXEC statements to PRINT, manually changed the fields where the auto generated fields were to long and created _shadow table manually.
modified 1-Aug-15 17:36pm.
|
|
|
|
|
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No rows were deleted.
A problem occurred attempting to delete row 1.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------
Fix For the above Error:
=======================
addition of SET NOCOUNT ON; for each triggers fixes.
|
|
|
|
|
can you tell me where to insert the set nocount on; in the code? thanks
|
|
|
|
|
Were you able to fix this? I am also facing this issue and I don't get why this is happening...
|
|
|
|
|
Thank you very much for taking the time to code and post this example. I am using it now to track down a problem of table rows inexplicable disappearing.
|
|
|
|
|
Hi Cedric
Fantastic job, well illustrated and very well explained.
Keep up the good work !
Marc-Olivier
Web and application developer
marckwest001 @yahoo.com
|
|
|
|
|
Very good article Code worked like advertised on SQL 2012 also.
|
|
|
|
|
Effective and simple implementation of an audit trail!
|
|
|
|
|
Thank you for a great solution!
I encountered one issue regarding the length of varchar and nvarchar columns. GenerateAuditTrail generates columns with wrong length for nchar and nvarchar columns.
SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
b.Length in this case returns bytes needed in order to contain the value and not the character length. An varchar(10) has a length of 10 bytes. nvarchar(10), however, has a byte length of 20 bytes. In order to generate shadow tables with the right column length, you have to treat nachr and nvarchar columns differently.
IF @ColumnType in ('binary', 'char', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END
IF @ColumnType in ('nchar', 'nvarchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast((@ColumnLength / 2 ) as varchar(10)) + ') '
END
For ncahr and nvarchar fields, you need to divide the length by two.
Best
Nasser
|
|
|
|
|
|
I just realized after I posted it that the code may not work for fields with special characters. You can change the CHARINDEX() code to
PATINDEX('%[^a-z]%', @ColumnName) > 0
SET @ListOfFields = @ListOfFields + CASE WHEN PATINDEX('%[^a-z]%', @ColumnName) > 0 THEN QUOTENAME(@ColumnName) ELSE @ColumnName END + ','
SET @CreateStatement = @CreateStatement + CASE WHEN PATINDEX('%[^a-z]%', @ColumnName) > 0 THEN QUOTENAME(@ColumnName) ELSE @ColumnName END + ' ' + @ColumnType
|
|
|
|
|
Thanks, I had this same problem.
Do you know if the original creator of the procedure still is around to read/update the solution?
|
|
|
|
|
I ran into an issue with tables with a LOT of columns, so I modified the code to remove '[ ]' where not needed as well as the section where it adds NULL/NOT NULL. Since NULL is the default, I removed it from the generated code.
CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + ' NOT NULL'
SET @CreateStatement = @CreateStatement + ', '
Here is the whole code:
CREATE PROCEDURE [dbo].[sp_GenerateShadowTrail]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtention varchar(128) = '_Shadow',
@DropAuditTable bit = 0
AS
BEGIN
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END
IF @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC ('drop table ' + @TableName + @AuditNameExtention)
END
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
OPEN TableColumns
DECLARE @ColumnName nvarchar(250)
DECLARE @ColumnType nvarchar(100)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
DECLARE @CreateStatement nvarchar(MAX)
DECLARE @ListOfFields nvarchar(4000)
SET @ListOfFields = ''
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table already exists. Only triggers will be updated.'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ','
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']('
SET @CreateStatement = @CreateStatement + 'AuditId bigint IDENTITY (1,1) NOT NULL,'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ','
SET @CreateStatement = @CreateStatement + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ' ' + @ColumnType
IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ')'
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ')'
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + ' NOT NULL'
SET @CreateStatement = @CreateStatement + ', '
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
SET @CreateStatement = @CreateStatement + 'AuditAction char(1),'
SET @CreateStatement = @CreateStatement + 'AuditDate datetime NOT NULL ,'
SET @CreateStatement = @CreateStatement + 'AuditUser varchar(64),'
SET @CreateStatement = @CreateStatement + 'AuditApp varchar(128))'
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC (@CreateStatement)
SET @CreateStatement = 'ALTER TABLE ' + @Owner + '.[' + @TableName + @AuditNameExtention + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR AuditDate,'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR AuditUser,CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
PRINT 'Dropping triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')
PRINT 'Creating triggers'
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
END
|
|
|
|
|
yes! this worked. thanks.
|
|
|
|
|
I have a column name with a space in it [Name Here] and when I execute the SP it chokes on creating the triggers. I went to the trigger code and added +'['+@tablename+']'+ but that doesn't seem to do the trick. It also chokes on the audit name exension. Any advice?
SQL 2008
Table already exists. Only triggers will be updated.
Dropping triggers
Creating triggers
Msg 102, Level 15, State 1, Procedure tr_, Line 1
Incorrect syntax near 'CaseDetails'.
Msg 102, Level 15, State 1, Procedure tr_, Line 1
Incorrect syntax near '_shadow'.
|
|
|
|
|
Nice implementation, Very well done!
|
|
|
|
|
To reflect the correct user that triggered a delete a common solution is to update the ChangedBy column in the original record via the stored procedure before the delete is executed. A drawback is that the update of the ChangedBy field before the actual delete is also audited as a separate entry in the audit table. I therefore changed the Update trigger code. Since the update is reflected in the deleted and inserted tables I compare both and prevent to write in the audit table when the ChangedBy field is updated only. My first solution was flawed (don't program when you are too tired ). For my second approach I needed a user function to find the Primary Key fields. I joined the inserted and deleted tables on the primary keys and only imported those row that differed in one filed except the ChangedBy field. There might be abetter solution, but it worked for me.
Of course, the solution doesn't record changes when the ChangedBy column would be altered manually, for example in the SQL Management Console, but for my application this is not an issue. Optional the select could be extended to write a change of the ChangedBy column if the ChangedBy column is identical to the suser_name.
Change of field lists generation:
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],';
if (@ListOfFieldsi <> '')
Set @ListOfFieldsi = @ListOfFieldsi + ', '
SET @ListOfFieldsi = @ListOfFieldsi + 'i.[' + @ColumnName + ']';
if (@ColumnName <> @ChangedByColumnName)
begin
if (dbo.IsPrimaryColumn(@Tablename, @ColumnName) =1)
begin
if (@ListOfJoinPKs <> '')
Set @ListOfJoinPKs = @ListOfJoinPKs + ' AND '
SET @ListOfJoinPKs = @ListOfJoinPKs + 'i.' + '[' + @ColumnName + ']=' + 'd.[' + @ColumnName + ']'
end
else
begin
if (@ListOfWhereColumns <> '')
Set @ListOfWhereColumns = @ListOfWhereColumns + ' OR '
SET @ListOfWhereColumns = @ListOfWhereColumns + 'i.' + '[' + @ColumnName + ']<>' + 'd.[' + @ColumnName + ']'
end
end
END
Creation of Update Trigger:
Set @UpdateTrigger = 'CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFieldsi + ',''U'' FROM Inserted i inner join Deleted d '
if (@ListOfJoinPKs <>'')
Set @UpdateTrigger = @UpdateTrigger + 'ON ' + @ListOfJoinPKs
if (@ListOfWhereColumns<>'')
Set @UpdateTrigger = @UpdateTrigger + ' WHERE ' + @ListOfWhereColumns
EXEC (@UpdateTrigger)
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
function IsPrimaryKey
CRETAE FUNCTION [dbo].[IsPrimaryColumn]
(
-- Add the parameters for the function here
@TableName varchar(128),
@ColumnName varchar(128)
)
RETURNS int
AS
BEGIN
Declare @out as bit
--Declare helper to identify the position of the delim
if (exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND K.COLUMN_NAME = @ColumnName
and K.TABLE_NAME=@TableName))
Set @out=1
else
Set @out=0;
Return @out
END
|
|
|
|
|
I'm implementing this solution at the moment and realized that there is an overseen problem with cascading deletes. Since a solution for the correct delete audit record is to write the user in the ChangedBy field before the record is deleted. This solution however doesn't concider cascading deletes. So either cascading deletes need to be avoided or the SPs need to change all the ChangedBy fields in the cascade before calling the delete.
|
|
|
|
|
Hi,
I found your code very useful. I required few changes in it.
The data tables will be modified by another application and we have
usernames and passwords to log in into the application.So i wanted
the exact username and application name to go into audit tables
instead of sa and the sql application name.
Can you please help me in achieving this.
|
|
|
|
|
Add a field called UserId (or similar) to each table you want to audit.
Save the userId of the person making the changes to this field for each insert/update.
|
|
|
|
|
Create PROCEDURE [dbo].[sp_GenerateAuditTable]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtension varchar(128) = '_shadow',
@DropAuditTable bit = 0,
@AuditDatabaseName varchar(128) = null
AS
BEGIN
declare @sql nvarchar(4000)
-- Check if table exists
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END
IF @AuditDatabaseName is null
BEGIN
set @AuditDatabaseName = (select DB_NAME())
END
-- Check @AuditNameExtension
IF @AuditNameExtension is null
BEGIN
PRINT 'ERROR: @AuditNameExtension cannot be null'
RETURN
END
-- Drop audit table if it exists and drop should be forced
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(@AuditDatabaseName + N'[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
set @sql = 'drop table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
print @sql
EXEC (@sql)
END
-- Declare cursor to loop over columns
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
OPEN TableColumns
-- Declare temp variable to fetch records into
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
-- Declare variable to build statements
DECLARE @CreateStatement varchar(8000)
DECLARE @ListOfFields varchar(2000)
SET @ListOfFields = ''
declare @AuditTableExists int
declare @existsParams nvarchar(4000)
set @existsParams='@Exists int output'
set @sql = 'if exists (SELECT * FROM ' + @AuditDatabaseName + '.information_schema.tables WHERE table_schema=N''' + @Owner + ''' and table_name=''' + @TableName + @AuditNameExtension + ''') set @Exists=1 else set @Exists=0'
-- Check if audit table exists
print @sql
Exec sp_executesql @sql, @existsParams, @Exists=@AuditTableExists output
print @AuditTableExists
IF @AuditTableExists = 1
BEGIN
-- AuditTable exists, update needed
PRINT 'Table already exists. Only triggers will be updated.'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
-- AuditTable does not exist, create new
-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '
IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '
SET @CreateStatement = @CreateStatement + 'NULL, '
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
-- Create audit table
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
print @CreateStatement
EXEC (@CreateStatement)
-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
PRINT 'Dropping triggers'
set @sql = '
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Insert]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]'')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Update]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]'')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Delete]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]'')'
exec (@sql)
PRINT 'Creating triggers'
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Insert ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS INSERT INTO '+ @AuditDatabaseName + '.' + @Owner + '.' + @TableName + @AuditNameExtension + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted'
print @sql
EXEC (@sql)
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Update ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS INSERT INTO ' + @AuditDatabaseName + '.' + @Owner + '.' + @TableName + @AuditNameExtension + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted'
print @sql
EXEC (@sql)
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Delete ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS INSERT INTO ' + @AuditDatabaseName + '.' + @Owner + '.' + @TableName + @AuditNameExtension + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted'
print @sql
EXEC (@sql)
END
|
|
|
|
|
very nice code, just one thing:
In the line
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(@AuditDatabaseName + N'[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
the section: object_id(@AuditDatabaseName + N'[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') should be:
object_id(N'[' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']')
Thanks!
|
|
|
|
|