August 24, 2010 at 11:10 am
Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.
ALTER TRIGGER [dbo].[trgTAAdditionalWork] ON [dbo].[TAAdditionalWork]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE @User varchar(255)
DECLARE @EditTime smalldatetime
DECLARE @AuditType varchar(1)
DECLARE @InsertID int
DECLARE @DeleteID int
set @User = suser_sname()
set @EditTime = getdate()
SELECT @InsertID = TAAdditionalWorkID FROM Inserted
SELECT @DeleteID = TAAdditionalWorkID FROM Deleted
IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D' --delete
IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I' --insert
IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U' --update
IF @AuditType = 'I' OR @AuditType = 'U'
BEGIN
INSERT TAAdditionalWork_Audit
SELECT
@AuditType,
AuditDate = @EditTime,
EditedBy = @User,
TAAdditionalWorkID,
ACID,
ActionID,
CommissionedID,
DtRevisedFinalSpec,
DtActualFinalSpec,
SpecSent,
ContainsCIC,
Stripped,
StrippedBy1ID,
StrippedBy2ID
FROM Inserted
END
IF @AuditType = 'D'
BEGIN
INSERT TAAdditionalWork_Audit
SELECT
@AuditType,
AuditDate = @EditTime,
EditedBy = @User,
TAAdditionalWorkID,
ACID,
ActionID,
CommissionedID,
DtRevisedFinalSpec,
DtActualFinalSpec,
SpecSent,
ContainsCIC,
Stripped,
StrippedBy1ID,
StrippedBy2ID
FROM Deleted
END
Thanks for any help. I've often looked at it and it looks okay to me - but in the Audit table there are thousands of duplicate entries recorded as taking place at exactly the same time.
August 24, 2010 at 12:34 pm
sku370870 (8/24/2010)
Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.
ALTER TRIGGER [dbo].[trgTAAdditionalWork] ON [dbo].[TAAdditionalWork]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE @User varchar(255)
DECLARE @EditTime smalldatetime
DECLARE @AuditType varchar(1)
DECLARE @InsertID int
DECLARE @DeleteID int
set @User = suser_sname()
set @EditTime = getdate()
SELECT @InsertID = TAAdditionalWorkID FROM Inserted
SELECT @DeleteID = TAAdditionalWorkID FROM Deleted
IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D' --delete
IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I' --insert
IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U' --update
IF @AuditType = 'I' OR @AuditType = 'U'
BEGIN
INSERT TAAdditionalWork_Audit
SELECT
@AuditType,
AuditDate = @EditTime,
EditedBy = @User,
TAAdditionalWorkID,
ACID,
ActionID,
CommissionedID,
DtRevisedFinalSpec,
DtActualFinalSpec,
SpecSent,
ContainsCIC,
Stripped,
StrippedBy1ID,
StrippedBy2ID
FROM Inserted
END
IF @AuditType = 'D'
BEGIN
INSERT TAAdditionalWork_Audit
SELECT
@AuditType,
AuditDate = @EditTime,
EditedBy = @User,
TAAdditionalWorkID,
ACID,
ActionID,
CommissionedID,
DtRevisedFinalSpec,
DtActualFinalSpec,
SpecSent,
ContainsCIC,
Stripped,
StrippedBy1ID,
StrippedBy2ID
FROM Deleted
END
Thanks for any help. I've often looked at it and it looks okay to me - but in the Audit table there are thousands of duplicate entries recorded as taking place at exactly the same time.
It looks ok-ish, but if you have to check what kind of operation is occuring, and have a seperate branch for each, it really should be two different triggers. Generally you want to avoid constructs like this:
SELECT @InsertID = TAAdditionalWorkID FROM Inserted
SELECT @DeleteID = TAAdditionalWorkID FROM Deleted
Although you're not actually using the variables for anything but a NULL check, it still sends a "smells funny" vibe any time I see something like this in a trigger.
Aside from that small weirdness, I don't see any reason why it should be inserting duplicate rows into your audit table.
August 25, 2010 at 1:38 am
If you insert multiple records into TAAdditionalWork then you will get "duplicate" entries based on AuditDate in your Audit table. However I would not expect to see duplicates across the entire record unless your original data is duplicated. Are you saying the entire record is duplicated or just the AuditDate?
August 25, 2010 at 6:09 am
Thanks for the replies.
The whole record is duplicated.
August 25, 2010 at 6:40 am
Do you have any other triggers associated with these tables?
August 25, 2010 at 10:49 pm
Hi ,
I too faced the similar problems and i came up with a solution. But before telling that, i would like 2 ask the following questions,
1. Did you tried to insert the data's directly in back end ? ? Still yor are getting duplicates ? ?
August 26, 2010 at 2:33 am
sku370870 (8/24/2010)
Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.
It is not correctly written. But the errors I see in this trigger can't cause duplicated entries in the audit tables. I suggest you check if there are other triggers defined on the tables - maybe old ones that should have been replaced but were left in place when a new trigger was defined?
Also, does your code use the new MERGE statement? I know that a MERGE can fire insert, update, AND delete triggers; I do not know if a single trigger for all these operations is fired once or multiple times (and I can't test it, as I don't have SQL2008 installed right now).
The most important error is that this trigger is built on the assumption of a single row being inserted, deleted, or updated. A trigger is fired once per executed statement, not once per affected row; the "inserted" and "deleted" pseudo-tables contain all affected rows. "SELECT @InsertID = TAAdditionalWorkID FROM Inserted" will set the variable to one of the values in the pseudo-table, with no way to predict which one it will be. I'm afraid that your main problem is not duplicated audit entries, but missing audit entries.
Another potential future problem is the use of INSERT without specifying the column list for the target table. A future change to the audit table layout might cause this code to switch columns or load columns with wrong data. If you're lucky, you get a conversion error. If you're unlucky, it might takes months before you notice the problem, and then you have a lot of manual cleanup to do...
August 26, 2010 at 3:27 am
select B.Name as TableName,A.name as TriggerName
from sysobjects A,sysobjects B
where A.xtype='TR'
AND A.parent_obj = B.id
Make use of the abocve query and check for other triggers that are involved in the table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply