Why do you need to auto-generate the trigger? If you know your table and the structure of that table, create your triggers with the right code, not auto-generated.


>Hi all -
>
>I have some code that I found on the internet to create triggers that will populate my audit table for updates, inserts and deletes. I am sorry I have forgotten where I found it but it works well. My problem is that I want to carry another field value (TIPID) that is not involved in the transaction. So I am trying to modify the code accordingly and having some difficulty. The working code is below. I have added the @TipID parameter definition and also in the field list of the select statement. The line two lines later is commented out. This is the line I am having trouble with. No matter how I code it, there is an error that says it cannot convert the varchar value to numeric (arithmetic overflow error). TipID is defined as numeric(8,2) in both tables.
>
>Thanks for any help here.
>
>

>USE [TipData]
>GO
>/****** Object:  Trigger [dbo].[TIP_Projects_ChangeTracking]    Script Date: 02/26/2014 16:59:06 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>ALTER trigger [dbo].[TIP_Projects_ChangeTracking] on [dbo].[TIP_Projects] for insert, update, delete
>as
>declare @bit int ,
>@field int ,
>@maxfield int ,
>@char int ,
>@fieldname varchar(128) ,
>@TableName varchar(128) ,
>@PKCols varchar(1000) ,
>@sql varchar(2000), 
>@UpdateDate varchar(21) ,
>@UserName varchar(128) ,
>@Type char(1) ,
>@PKFieldSelect varchar(1000),
>@PKValueSelect varchar(1000),
>@TipID numeric(8,2)
>
>select @TableName = 'TIP_Projects'
>
>-- date and user
>select @UserName = system_user ,
>	@UpdateDate = convert(varchar(8), 
>	getdate(), 112) + ' ' + convert(varchar(12), 
>	getdate(), 114)
>
>-- Action
>if exists (select * from inserted)
>if exists (select * from deleted)
>select @Type = 'U'
>else
>select @Type = 'I'
>else
>select @Type = 'D'
>
>-- get list of columns
>select * into #ins from inserted
>select * into #del from deleted
>
>-- Get primary key columns for full outer join
>select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
>from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
>where pk.TABLE_NAME = @TableName 
>and CONSTRAINT_TYPE = 'PRIMARY KEY' 
>and c.TABLE_NAME = pk.TABLE_NAME 
>and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
>
>-- Get primary key fields select for insert
>select @PKFieldSelect = coalesce(@PKFieldSelect + '+','') + '''' + COLUMN_NAME + '''' 
>from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
>where pk.TABLE_NAME = @TableName
>and CONSTRAINT_TYPE = 'PRIMARY KEY'
>and c.TABLE_NAME = pk.TABLE_NAME
>and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
>
>select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
>from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
>where  pk.TABLE_NAME = @TableName   
>and CONSTRAINT_TYPE = 'PRIMARY KEY'   
>and c.TABLE_NAME = pk.TABLE_NAME   
>and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
>
>if @PKCols is null
>begin
>raiserror('no PK on table %s', 16, -1, @TableName)
>return
>end
>
>select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
>while @field < @maxfield
>begin
>select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
>select @bit = (@field - 1 )% 8 + 1
>select @bit = power(2,@bit - 1)
>select @char = ((@field - 1) / 8) + 1
>if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
>begin
>select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
>select @sql = 'insert audit (Type, TipID, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)'
>select @sql = @sql + ' select ''' + @Type + ''''
>--select @sql = @sql + ',convert(numeric(8,2),' + @TipID + ')'
>select @sql = @sql + ',''' + @TableName + ''''
>select @sql = @sql + ',' + @PKFieldSelect
>select @sql = @sql + ',' + @PKValueSelect
>select @sql = @sql + ',''' + @fieldname + ''''
>select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
>select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
>select @sql = @sql + ',''' + @UpdateDate + ''''
>select @sql = @sql + ',''' + @UserName + ''''
>select @sql = @sql + ' from #ins i full outer join #del d'
>select @sql = @sql + @PKCols
>select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname 
>select @sql = @sql + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
>select @sql = @sql + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
>exec (@sql)
>end
>end
>
>