?
Solved

adding trigger to sql tables

Posted on 2015-01-27
6
Medium Priority
?
356 Views
Last Modified: 2015-02-01
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 ?
0
Comment
Question by:AZZA-KHAMEES
6 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 501 total points
ID: 40573791
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
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 501 total points
ID: 40574118
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40574721
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40575040
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
 

Author Comment

by:AZZA-KHAMEES
ID: 40575046
thank you all for the reply
can you please provide me with a site for the best and simple practice on this
0
 
LVL 36

Assisted Solution

by:ste5an
ste5an earned 498 total points
ID: 40575067
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question