adding trigger to sql tables

Hi experts,
i am trying to create a trigger to sql table in my database to track any insert, update and delete to any table any fields, this subject was new to me, i searched the net and i found a very helpfull artical that 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.

can anyone help me
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

Open in new window

how can i skip some of the column ?
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
If this subject is new for you, the best course is to start with something trivial, and create some simple trigger - rather than write one for several pages. Once you get a feeling of how everything it works, then you can move to the real thing.
0
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
if you just want to skip some tables, add them after this line
AND TABLE_NAME!= 'Audit'

e.g.
AND TABLE_NAME!= 'BahbyGrade'

But at first glance it looks like it's failing to create the audit because of some text type columns. I agree with vadim, you should really understand what you are doing a little better before enabling a million triggers all over your database. you probably don't need everything this script does and it can have significant performance impacts.
0
 
ste5anSenior DeveloperCommented:
First of all: Why don't you use CDC?

Then you approach is imho incorrect. Triggers should be specific to their tables and tasks. Thus you should not generate triggers with dynamic SQL in the body.
Instead of this: Collect the information for your tables and columns and create a static SQL as body using fixed table and column names.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Vadim RappCommented:
If your sql server is 2008 or higher, maybe you don't need to create triggers at all, since it includes its own audit feature. Google "sql server audit" for many articles.
0
 
AZZA-KHAMEESAuthor Commented:
thank you all for the reply
can you please provide me with a site for the best and simple practice on this
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.