Adding trigger to sql tables

Question

I am trying to create a trigger for sql table in my database to track any insert, update and delete of any table and any fields, this subject was new to me, I searched on net and i found a very helpful article to do this, but when I run this query i got several error message.

Msg 8197, Level 16, State 4, Procedure BahbyGrade_ChangeTracking, Line 3 The object 'BahbyGrade' does not exist or is invalid for this operation.

Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 69 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 71 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Msg 311, Level 16, State 1, Procedure OutSide_ChangeTracking, Line 69 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Msg 311, Level 16, State 1, Procedure OutSide_ChangeTracking, Line 71 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Msg 8197, Level 16, State 4, Procedure PersonnelNew_ChangeTracking, Line 3 The object 'PersonnelNew' does not exist or is invalid for this operation.

USE pr1
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')

CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserName varchar(128)
)
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON;
SELECT @TABLE_NAME = MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.Tables 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
WHILE @TABLE_NAME IS NOT NULL

BEGIN

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

SELECT @sql = 

create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' 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)

select @TableName = ''' + @TABLE_NAME+ '''

-- 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, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''

select @sql = @sql + '' select '''''' + @Type + ''''''''

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

SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
END

how can i skip some of the column ?

http://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database

Answer 1

A couple days ago I was having a similar issue. I modified the version originally found on the website you posted above.

The code below allows you to include tables that have text, ntext or image columns. however there are a couple of things to keep in mind.

1. due to how mssql works you cannot reference text, ntext, or image columns except in "INSTEAD OF" triggers. to get around this i only check if one of these forbidden column types was included in the update. i can't tell whether it actually changed, or capture its old value. so any time one of these columns are included in the update, i assume it has changed.
2. tables with text, ntext or image columns will need their triggers updated if you make any schema changes. or a table will also need its trigger updated it you change any columns to or from text, ntext or image data type.

However, If you have 2008 or later It is probably recommended to use SQL's built in change tracking

--Author: Rickac
--Date: 2015.03.23
--purpose: track changes to a database
--this has been modified but was originally based on http://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database
--note: this cannot properly detect changes to text,ntext or image columns.  it will track any update that includes one of these columns even if no change was made.
--      tables with text, ntext or image columns will need their trigger updated if a column is added to or removed from that table or in cases where a data type changes either to or from some other type to a text, ntext, or image type.

USE MY_DB -- TODO change this to your DB
GO

--hold some global script variables.
create table #vars (name sysname, value varchar(8000))
insert #vars values ('AUDIT_TABLE_NAME','Audit') -- this is the name you want the audit table to be.
insert #vars values ('TRIGGER_SUFFIX','_ChangeTracking') -- this is used for naming the trigger

Declare @sql varchar(max),@AUDIT_TABLE_NAME sysname
--retrieve global variable
select @AUDIT_TABLE_NAME=value from #vars where name='AUDIT_TABLE_NAME'

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= @AUDIT_TABLE_NAME)
set @sql='CREATE TABLE '+ @AUDIT_TABLE_NAME +'
(
AuditID int IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128),
query varchar(4000)
)'
exec(@sql)
GO

DECLARE @sql varchar(max), @TABLE_NAME sysname, @AUDIT_TABLE_NAME sysname, @TRIGGER_SUFFIX varchar(255)
SET NOCOUNT ON
--retrieve global variable
select @AUDIT_TABLE_NAME=value from #vars where name='AUDIT_TABLE_NAME'
select @TRIGGER_SUFFIX=value from #vars where name='TRIGGER_SUFFIX'


DECLARE table_cursor CURSOR FOR 
  SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.Tables
    WHERE
      TABLE_TYPE= 'BASE TABLE'
      AND TABLE_NAME not in ('sysdiagrams',@AUDIT_TABLE_NAME)
      --use the below clause if you only want a specific table or list of tables
      -- AND TABLE_NAME in ('mytable')
    ORDER BY TABLE_NAME

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print 'adding '+@TABLE_NAME+ @TRIGGER_SUFFIX 
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ @TRIGGER_SUFFIX +''', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ @TRIGGER_SUFFIX)
set @sql =
'
create trigger ' + @TABLE_NAME+ @TRIGGER_SUFFIX +' on [' + @TABLE_NAME+ '] for insert, update, delete
as
set nocount on

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(MAX),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000),
@changes int


select @TableName = ''' + @TABLE_NAME+ '''
-- 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
'
declare @contains_forbidden_columns int
set @contains_forbidden_columns=0
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLE_NAME and DATA_TYPE in ('text','ntext','image'))
BEGIN
--darn, they have a text, ntext, or image column that means our job just got difficult
--lets try to list the columns individually.
set @contains_forbidden_columns=1

--get the primary keys so we can run a join
DECLARE @PKCols varchar(1000), @ColumnList varchar(max)
--reset our vars
set @ColumnList=NULL
set @PKCols=NULL

-- Get primary key columns for join
select @PKCols = coalesce(@PKCols + ' and', '') + ' t.[' + c.COLUMN_NAME + '] = c.[' + c.COLUMN_NAME +']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TABLE_NAME
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

--get the column list (excluding any text,ntext, or image columns)
select @ColumnList = coalesce(@ColumnList + ',', '') + ' c.['+ c.COLUMN_NAME +']'
 from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @TABLE_NAME and DATA_TYPE not in ('text','ntext','image')

select @ColumnList = coalesce(@ColumnList + ',', '') + ' t.['+ c.COLUMN_NAME +']'
 from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @TABLE_NAME and DATA_TYPE in ('text','ntext','image')

set @sql=@sql+
'
select '+@ColumnList+' into #ins from ['+@TABLE_NAME+'] t join inserted c on ('+@PKCols+')
select '+@ColumnList+' into #del from ['+@TABLE_NAME+'] t join deleted c on ('+@PKCols+')
'

END
ELSE
BEGIN

set @sql=@sql+
'
select * into #ins from inserted
select * into #del from deleted
'

END

set @sql=@sql+
'
CREATE TABLE #inputbuffer 
 (
  EventType nvarchar(30), 
  Parameters int, 
  EventInfo nvarchar(4000)
 )

 INSERT INTO #inputbuffer EXEC (''DBCC INPUTBUFFER(''+@@SPID+'') with NO_INFOMSGS'')

-- 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_TABLE_NAME+' (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName, query)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
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 + '',(SELECT top 1 EventInfo FROM #inputbuffer)''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
'
declare @where varchar(1000)
set @where=
'
  select @sql = @sql + '' where ''
  select @sql = @sql + ''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)''
'

--does this table contain forbidden columns?
if @contains_forbidden_columns>0
BEGIN
--yes this table contains forbidden columns so we need to adjust the trigger accordingly and detect if this particular column is a forbidden type
set @sql=@sql+
'
-- see if this column contains a forbidden value
if not exists(select ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '''+@TABLE_NAME+''' and DATA_TYPE in (''text'',''ntext'',''image'') and sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(),ORDINAL_POSITION)!=0 and COLUMN_NAME=@fieldname)
BEGIN
  -- it does not contain a forbidden value so add the where clause for a clean column
'
+@where+
'
END
'
END
ELSE
BEGIN
--this table does not contain forbidden columns
set @sql=@sql+@where

END

set @sql=@sql+
'
exec (@sql)
end
end
'

SELECT @TABLE_NAME as 'table',@sql as 'trigger'
EXEC(@sql)

FETCH NEXT FROM table_cursor INTO @TABLE_NAME

END 

--******************************************
--BEGIN cleanup
CLOSE table_cursor;
DEALLOCATE table_cursor;

IF OBJECT_ID('tempdb..#vars') IS NOT NULL
BEGIN
  Drop table #vars
END
--END cleanup
--******************************************

Answer 2

What I have done in the past to get around the fact that you can't reference text and ntext is to simply join back to the orginal table to get those values. assumning the table you have the trigger on is called mytable, you can see thetext field values this way and use that technique to write your insert to the audit table:

SELECT i.id, t.mytextfield
from inserted i
join mytable t on t.id = i.id

Unfortunately you won't be able to get the old values but only the new one. But if you have the audit running over time, you should be able to find the last value stored before that change if need be. You can start the process by copying that data manually to the audit table, so you have teh values at the start of auditing. Or I suppose you could have a before trigger that captures the before values of these fields and and after trigger that captures the new ones. (I haven't tried that)

But the best bet is to bite the bullet and do what it takes to get rid of those data types. They are deprecated. Varchar(max) and nvarchar(max) are much better in any event.