Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create DML trigger for single table for a Database in SQL 2008R2


Create DML trigger for single table for a Database in SQL 2008R2

Author
Message
dba.sql29
dba.sql29
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 1206
Create DML trigger for single table for a Database in SQL 2008R2

Hi,

I have a requirement to Create DML trigger for single table ( check for any Inserts, Updates & Delete ) in SQL 2008R2 and store Database Name, Event Type ,Object Name, TSQL to a different database. For example create Trigger on Database A Table and store result on Database B. I have the following the script to capture DML events on a Database, but it’s not sending result to B database.Any help would be greatly appreciated.

Thanks in advance
create table Audit (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go

ALTER trigger tr_trigtest on dbo.CER_SEDA_DOMAIN_A 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) ,
@PKSelect varchar(1000)

select @TableName = 'dbo.CER_SEDA_DOMAIN_A'

-- 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 select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+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 CMMonitor.[dbo].Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
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
go
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7813 Visits: 8752
Did you verify your linked server permissions on the secondary database server to make sure it can actually access the database / table and write to it?

EDIT: And are you sure your trigger is actually returning any values?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
This is a difficult situation. The trigger you have created is trying to deal with a situation that I find to be very painful and not very useful in the long run. This Audit table will hold values for a given column along with the date it was modified. Here is the real issue with this type of design. Consider being asked to figure out all the values for a given primary key value on a given date. Think about how difficult this is going to be when you have a table with only 10 o 15 columns. You are going to have to write a big ugly nasty query just to determine the values for each column. Consider a much wider table and how difficult that is going to be.

Now lets examine the actual trigger. There are some major issues with this trigger. Performance is going to be a serious issue here because of the complexity of what you are doing. You have built a while loop inside this trigger to insert a new row into the audit table for each column in CER_SEDA_DOMAIN_A. Looping in t-sql is almost always very slow.

If this was a system I was working with I would consider rethinking the audit structure here and this can become a pretty simple process.

For anybody else stumbling in here I formatted the posted code so we can see it easier.


ALTER TRIGGER tr_trigtest ON dbo.CER_SEDA_DOMAIN_A
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)
,@PKSelect VARCHAR(1000)

SELECT @TableName = 'dbo.CER_SEDA_DOMAIN_A'

-- 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 select for insert
SELECT @PKSelect = coalesce(@PKSelect + '+', '') + '''<' + COLUMN_NAME + '=''+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 CMMonitor.[dbo].Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'

SELECT @sql = @sql + ' select ''' + @Type + ''''

SELECT @sql = @sql + ',''' + @TableName + ''''

SELECT @sql = @sql + ',' + @PKSelect

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
GO



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search