Audit Trigger firing twice

Question

Post reply

Audit Trigger firing twice

deadtrees

Old Hand

Points: 303
More actions
February 25, 2016 at 9:54 am

#308817

I found some great Audit trigger code on the interwebs and have been using it successfully for some time without issue. I recently created a new table and applied the trigger code to the new table and it is firing twice and I can't figure out why. I tried changing the Select Distinct to Select Top 1 in the code at the bottom that builds the insert logic but to no avail. Access front end, SQL 2012 Server backend
Can some expert eyes zero in on what might be the problem?
Here's the scripted table with key data
CREATE TABLE [dbo].[TaskReason](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Reason] [nvarchar](255) NULL,
[Task] [nvarchar](255) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
[Inactive] [bit] NULL DEFAULT ((0)),
[AlwaysOn] [bit] NULL DEFAULT ((0))
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [LIMS_DATA_Production]
GO
/****** Object: Trigger [dbo].[tr_taskreason] Script Date: 2/25/2016 8:26:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create trigger [dbo].[tr_taskreason] on [dbo].[TaskReason] for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@doCode bit,
@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 = 'TaskReason'
-- 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
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))'
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
IF @Type = 'I' or @Type = 'D'
BEGIN
SET @maxfield = 1
SET @field = 0
END
ELSE
BEGIN
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
End
Set @doCode = 0
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
if (select name from syscolumns where colid = @field and id = (select id from sysobjects where name = 'TaskReason')) <> 'SSMA_TimeStamp'
set @doCode = 1
else
set @doCode = 0
END
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field AND ORDINAL_POSITION <> 4
select @sql = 'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select Distinct''' + @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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) How do you KNOW it is firing twice?

2) Is anyone using MERGE? That can cause what you are seeing.

3) Any other triggers floating around?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004589 More actions · Answer 2

deadtrees (2/25/2016)
I found some great Audit trigger code on the interwebs ...

No sir... that's some of the worst audit code ever. It's one of those damned "generic" triggers that makes a copy of the INSERTED and DELETED tables and I'm here to tell you from personal experience that it's actually a major performance problem. Even if it were to be coded as a CLR, it's comparatively and hugely slow. I replaced all such triggers in my "money maker" database because of the performance problems. It was taking 4 minutes to update just 4 columns on just 10,000 rows on a wide table. After writing code to generate hardcoded triggers directly from the table meta-data, that evolution dropped to just 800 milliseconds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

deadtrees Old Hand Points: 303 More actions · Answer 3

Good question. Turns out it is only happens on Update event on the Task column, and I'm determining that through the double entry with the same timestamp whenever that column is updated. (Queried Audit Trail for the table in question)

TYPETableNamePKFieldNameOldValue NewValue UpdateDate UserName

UTaskReason79Task TEST ALWAYS ON TASKTEST ALWAYS ON TAS2/25/2016 8:14:15 AMWABC\user101

UTaskReason79Task TEST ALWAYS ON TASTEST ALWAYS ON TASK2/25/2016 8:22:43 AMWABC\user101

UTaskReason79Task TEST ALWAYS ON TASKTEST ALWAYS ON TAS2/25/2016 8:27:55 AMWABC\user101

UTaskReason79Inactive1 0 2/25/2016 9:55:12 AMWABC\user101

ITaskReason80ID 80 2/25/2016 10:01:23 AMWABC\user101

deadtrees Old Hand Points: 303 More actions · Answer 4

@jeff

Fair enough, but this was a 'database that proved to be too useful' scenario. As a result it's getting picked up to get turned into an enterprise application that wasn't pieced together from the internet, but until that time solutions are appreciated.

Jeff Moden SSC Guru Points: 1004589 More actions · Answer 5

deadtrees (2/25/2016)
@Jeff
Fair enough, but this was a 'database that proved to be too useful' scenario. As a result it's getting picked up to get turned into an enterprise application that wasn't pieced together from the internet, but until that time solutions are appreciated.

That's precisely why I responded previously. It didn't want a possible enterprise application to have the kinds of problems that I've seen on the systems that I've inherited.

My audit trigger generator is tuned specifically to the needs of the current company that I work for and would do you little good and I don't know your standards so I can't even tweak it for you. My recommendation again would be to write code that looks at a table and writes static trigger code for you. One of the very wide 147 column tables (I didn't design it) suffers virtually no INSERT/UPDATE performance degradation even though the code generate 147 IF UPDATE(column) queries.

And, just to be sure... I wasn't slamming you. I was warning you that the code you found is a real killer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

see this thread....the trigger is "almost" like yours...

towards the end of the thread I posted some test scripts.

I have used this trigger....but only on tables where the data is infrequently updated (eg master data customer/address/product etc).....and only for updates/deletes.

http://www.sqlservercentral.com/Forums/Topic1544629-146-1.aspx

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Audit Trigger firing twice

Cookies on SQLServerCentral