Solved

adding trigger to sql tables

Posted on 2015-01-27
6
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 167 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 167 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 34

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 34

Assisted Solution

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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