September 30, 2005 at 8:07 am
Having seen the article for automating trigger generation (http://www.sqlservercentral.com/scripts/contributions/1073.asp), I have been working on something similar myself, which will suit my needs a bit more than what was there. I need a single database on each server, into which I can log all data changes for other databases. Into this database, I will log each change to a field in a database, using a GUID reference between the Log Table and the record in the table being logged. As I am logging for more than one database, I wanted to capture database, table name, column name, user name, application name (eg Query Analyser), host name and date.
I created a Log table as follows:
CREATE TABLE
[dbo].[T__Log_Data_Change] (
[UI_Record] [uniqueidentifier] NOT NULL ,
[VC_Database] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_Table] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_Column] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_User] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_Host_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_App_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_Entry] [datetime] NOT NULL ,
[VC_Action] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VC_Old_Data] [nvarchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VC_New_Data] [nvarchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
I will eventually create a stored procedure DBO_AllTriggers, which will loop through all tables in a database and call my other SP, DBO_Trigger to create the DDL for the triggers for the table.
The Code for DBO_Trigger is as follows:
ALTER PROCEDURE dbo.DBO_Trigger
(
@strTableName varchar(200),
@strTriggerDatabaseandTableName varchar(200) = 'ldc0ukdb.dbo.T__Log_Data_Change',
@strExec bit = 0
)
AS
-- This stored procedure will create Log Triggers for a Given Table in a database
set nocount on
set CONCAT_NULL_YIELDS_NULL off
declare @intColid int
declare @strColName varchar(300)
declare @strInsertTriggerCommand varchar(8000)
declare @strInsertTriggerName varchar(200)
declare @strInsertTriggerStart varchar(500)
declare @strInsertTriggerDropCommand varchar(500)
declare @strUpdateTriggerCommand varchar(8000)
declare @strUpdateTriggerDropCommand varchar(500)
declare @strUpdateTriggerName varchar(200)
declare @strUpdateTriggerStart varchar(500)
declare @strDeleteTriggerCommand varchar(8000)
declare @strDeleteTriggerDropCommand varchar(500)
declare @strDeleteTriggerName varchar(200)
declare @strDeleteTriggerStart varchar(500)
declare @strTriggerCommon varchar(2000)
declare @crlf varchar(2)
set @crlf = char(13) + char(10)
declare @tab varchar(2)
set @tab = ' '
-- Temp table used as it is quicker than a cursor.
create table #columns
(in_colid int identity(1,1),
vc_colname nvarchar(300),
bt_used bit)
-- Common Bit for All Triggers
set @strTriggerCommon = 'AS'
set @strTriggerCommon = @strTriggerCommon + @crlf + '--Created with DBO_Triggers by: ' + suser_sname() + ' On: ' + convert(varchar(20), getdate())
set @strTriggerCommon = @strTriggerCommon + @crlf
set @strTriggerCommon = @strTriggerCommon + @crlf + 'Set NoCount On'
set @strTriggerCommon = @strTriggerCommon + @crlf
set @strTriggerCommon = @strTriggerCommon + @crlf + 'declare @UserName varchar(50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'declare @HostName varchar(50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'declare @AppName varchar(50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'declare @ActionTime datetime'
set @strTriggerCommon = @strTriggerCommon + @crlf
set @strTriggerCommon = @strTriggerCommon + @crlf + 'set @UserName = left(suser_sname(),50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'set @HostName = left(host_name(),50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'set @AppName = left(app_name(),50)'
set @strTriggerCommon = @strTriggerCommon + @crlf + 'set @ActionTime = getdate()'
set @strTriggerCommon = @strTriggerCommon + @crlf
-- Trigger Names
set @strInsertTriggerName = 'dbo.TR_' + @strTableName + '_I'
set @strUpdateTriggerName = 'dbo.TR_' + @strTableName + '_U'
set @strDeleteTriggerName = 'dbo.TR_' + @strTableName + '_D'
-- Trigger Starts
set @strInsertTriggerStart = 'CREATE TRIGGER ' + @strInsertTriggerName
set @strInsertTriggerStart = @strInsertTriggerStart + @crlf + 'ON dbo.' + @strTableName
set @strInsertTriggerStart = @strInsertTriggerStart + @crlf + 'FOR INSERT'
set @strInsertTriggerStart = @strInsertTriggerStart + @crlf
set @strUpdateTriggerStart = 'CREATE TRIGGER ' + @strUpdateTriggerName
set @strUpdateTriggerStart = @strUpdateTriggerStart + @crlf + 'ON dbo.' + @strTableName
set @strUpdateTriggerStart = @strUpdateTriggerStart + @crlf + 'FOR UPDATE'
set @strUpdateTriggerStart = @strUpdateTriggerStart + @crlf
set @strDeleteTriggerStart = 'CREATE TRIGGER ' + @strDeleteTriggerName
set @strDeleteTriggerStart = @strDeleteTriggerStart + @crlf + 'ON dbo.' + @strTableName
set @strDeleteTriggerStart = @strDeleteTriggerStart + @crlf + 'FOR DELETE'
set @strDeleteTriggerStart = @strDeleteTriggerStart + @crlf
-- Create the Drop Commands
set @strInsertTriggerDropCommand = 'if exists (select * from dbo.sysobjects where id = object_id(''' + @strInsertTriggerName + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) drop trigger ' + @strInsertTriggerName
set @strUpdateTriggerDropCommand = 'if exists (select * from dbo.sysobjects where id = object_id(''' + @strUpdateTriggerName + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) drop trigger ' + @strUpdateTriggerName
set @strDeleteTriggerDropCommand = 'if exists (select * from dbo.sysobjects where id = object_id(''' + @strDeleteTriggerName + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) drop trigger ' + @strDeleteTriggerName
-- Initialise the Trigger Commands
set @strInsertTriggerCommand = ''
set @strUpdateTriggerCommand = ''
set @strDeleteTriggerCommand = ''
-- Now Start to build the Rest of the triggers.
-- Populate the columns table
Insert into #columns
(vc_colname, bt_used)
SELECT COLUMN_NAME, 0
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @strTableName and COLUMN_NAME not like 'UI%'
while exists (select * from #columns where bt_used = 0)
begin
-- Get the column name
select top 1 @intColid = in_colid, @strColName = vc_colname
from #columns where bt_used = 0
set @strInsertTriggerCommand = @strInsertTriggerCommand + @crlf + 'INSERT INTO ' + @strTriggerDatabaseandTableName
set @strInsertTriggerCommand = @strInsertTriggerCommand + @crlf + 'select i.UI_Record, ''' + db_name() + ''', ''' + @strTableName + ''', ''' + @strColName + ''', @UserName , @HostName , @AppName, @ActionTime, ''I'', null, convert(nvarchar(1500), i.' + @strColName + ')'
set @strInsertTriggerCommand = @strInsertTriggerCommand + @crlf + 'FROM inserted i'
set @strInsertTriggerCommand = @strInsertTriggerCommand + @crlf
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + 'IF UPDATE(' + @strColName + ')'
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + 'begin'
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + @tab + 'INSERT INTO ' + @strTriggerDatabaseandTableName
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + @tab + 'select i.UI_Record, ''' + db_name() + ''', ''' + @strTableName + ''', ''' + @strColName + ''', @UserName , @HostName , @AppName, @ActionTime, ''U'', convert(nvarchar(1500), d.' + @strColName + '), convert(nvarchar(1500), i.' + @strColName + ')'
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + @tab + 'FROM inserted i left outer join deleted d on i.UI_Record = d.UI_Record'
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf + 'end'
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @crlf
set @strDeleteTriggerCommand = @strDeleteTriggerCommand + @crlf + 'INSERT INTO ' + @strTriggerDatabaseandTableName
set @strDeleteTriggerCommand = @strDeleteTriggerCommand + @crlf + 'select d.UI_Record, ''' + db_name() + ''', ''' + @strTableName + ''', ''' + @strColName + ''', @UserName , @HostName , @AppName, @ActionTime, ''D'', convert(nvarchar(1500), d.' + @strColName + '), null'
set @strDeleteTriggerCommand = @strDeleteTriggerCommand + @crlf + 'FROM deleted d'
set @strDeleteTriggerCommand = @strDeleteTriggerCommand + @crlf
-- Make sure we don't use it again.
update #columns
set bt_used = 1
where in_colid = @intColid
end
if @strExec = 1
begin
exec (@strInsertTriggerDropCommand)
exec (@strUpdateTriggerDropCommand)
exec (@strDeleteTriggerDropCommand)
exec (@strInsertTriggerStart + @strTriggerCommon + @strInsertTriggerCommand)
exec (@strUpdateTriggerStart + @strTriggerCommon + @strUpdateTriggerCommand)
exec (@strDeleteTriggerStart + @strTriggerCommon + @strDeleteTriggerCommand)
end
else
begin
print @strInsertTriggerDropCommand
print @strUpdateTriggerDropCommand
print @strDeleteTriggerDropCommand
print @strInsertTriggerStart + @strTriggerCommon + @strInsertTriggerCommand
print @strUpdateTriggerStart + @strTriggerCommon + @strUpdateTriggerCommand
print @strDeleteTriggerStart + @strTriggerCommon + @strDeleteTriggerCommand
end
RETURN
I execute the stored procedure using
dbo_trigger 'TABLENAME',default, 0
This works fine and generates all the triggers I need for most of the 59 tables in the first database in which I am going to perform auditing.
Given the table definition below, I get a funny result when I run the statement: dbo_trigger 'T_Process_Queue_VPT_Event_Header',default, 0
CREATE TABLE
[dbo].[T_Process_Queue_VPT_Event_Header] (
[IN_VPT_Event] [int] NOT NULL ,
[VC_Event_Reference] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IN_VPT_Customer] [int] NULL ,
[VC_CBM_UserID] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VC_Last_Change_DateTime] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VC_Event_Status] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VC_Event_Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DT_Instore_Start_Date] [datetime] NULL ,
[DT_Instore_End_Date] [datetime] NULL ,
[DT_Buying_Start_Date] [datetime] NULL ,
[DT_Buying_End_Date] [datetime] NULL ,
[IN_Likelihood] [int] NULL ,
[UI_Record] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
The Insert and Delete triggers are generated without a problem. Unfortunately, the Update trigger seems to lose bits of the string @strUpdateTriggerCommand, on the very last field for the table.
INSERT INTO ldc0ukdb.dbo.T__Log_Data_Change
select i.UI (*** rest of this line has disappeared??? ***)
FROM inserted i left outer join deleted d on i.UI_Record = d.UI_Record
I just cannot see why this is happening. I wondered whether I was running out of string space in the variable, but it only gets to about 5000 characters, so there is still plenty of room. I also tried disabling the generation of the insert and delete triggers, but still have no luck.
Apologies for a long post, I have been struggling with this for a few hours now, and cannot seem to get it right. I am running SQL Server 2000 SP3 (8.00.760). Also, apologies if this appears twice. I created the post, hit "Preview" and the whole lot disappeared, so I am not sure if it ever went.
Anyone got any ideas. I'm totally perplexed by this one.
Many Thanks
Graham Cottle
October 2, 2005 at 7:37 am
After a lot of fiddling, I found the answer myself:
It appears that a String Variable starts to have odd behaviour during lots of concatenation operations once it gets past 4096 characters in length. Don't know why on this. Any ideas?
So what I did was create a separate string variable for the "Insert" generation, build that up and then add it to my main Command variable once I had built the string. This then gave the correct result and I didn't suffer a loss of text.
The procedure will still suffer when the longer variable gets past 8000 characters, but I can live with that.
Thus the update code now looks like (tidied a bit more as well):
set @strUpdateTriggerSubCommand = 'IF UPDATE(' + @strColName + ')'
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + 'begin'
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + 'INSERT INTO ' + @strTriggerDatabaseandTableName
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + 'SELECT i.UI_Record, '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '''' + db_name() + ''', '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '''' + @strTableName + ''', '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '''' + @strColName + ''', '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '@UserName , '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '@HostName , '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '@AppName, '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '@ActionTime, '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + '''U'', '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + 'convert(nvarchar(1500), d.' + @strColName + '), '
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + @tab + 'convert(nvarchar(1500), i.' + @strColName + ')'
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @tab + 'FROM inserted i left outer join deleted d on i.UI_Record = d.UI_Record'
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + 'end'
set @strUpdateTriggerSubCommand = @strUpdateTriggerSubCommand + @crlf + @crlf
set @strUpdateTriggerCommand = @strUpdateTriggerCommand + @strUpdateTriggerSubCommand
I now get the desired results on all of my triggers. Luckily my tables don;t have too many fields, otherwise I will siffer again, but I think I can live with the consequences of that for the time being. If it becomes a big issue, then I can either script using VBScript and DMO, or go back to a manual operation.
Graham
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply