?
Solved

adding trigger to sql tables

Posted on 2015-01-27
6
Medium Priority
?
342 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 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 35

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 35

Assisted Solution

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

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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