Solved

adding trigger to sql tables

Posted on 2015-01-27
6
303 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 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 38

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 33

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 33

Assisted Solution

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now