April 13, 2002 at 12:24 pm
I have audit triggers on all tables. The strange thing happening is as follows. When I try to edit and save data from the front-end it doesnt update the information. The moment i remove the trigger the update starts working. Any suggestions??? Is this a problem with the trigger or middle-tier or front-end???
April 13, 2002 at 2:09 pm
Sounds like the trigger. Are you doing a rollback somewhere, or maybe it's an instead of trigger? Post the trigger code, we'll see if we can help you figure it out.
Andy
April 13, 2002 at 3:12 pm
CREATE TRIGGER TR_AuditDetail ON dbo.tblDetail
FOR UPDATE
AS
DECLARE @OldValue VARCHAR(255)
DECLARE @NewValue VARCHAR(255)
DECLARE @User VARCHAR(20)
DECLARE @OldValueMoney MONEY
DECLARE @NewValueMoney MONEY
DECLARE @dteOldValue DATETIME
DECLARE @dteNewValue DATETIME
DECLARE @intOldValue NUMERIC
DECLARE @intNewValue NUMERIC
DECLARE @key INT
DECLARE @FieldName VARCHAR(50)
SELECT @key=i.intDetailID, @User=i.vchEditedBy FROM inserted i INNER JOIN deleted d ON i.intDetailID=d.intDetailID
IF UPDATE(intReference)
BEGIN
SET @OldValue = NULL
SET @NewValue = NULL
SET @intOldValue = 0
SET @intNewValue = 0
SET @FieldName ='intReference'
SET @intOldValue = (SELECT intReference FROM DELETED WHERE intDetailID = @key)
SET @intNewValue = (SELECT intReference FROM INSERTED WHERE intDetailID = @key)
IF (@intOldValue <> @intNewValue)
BEGIN
SET @OldValue = CAST(@intOldValue AS VARCHAR)
SET @NewValue = CAST(@intNewValue AS VARCHAR)
INSERT INTO tblAudit (audit_log_type, audit_key, audit_OldValue, audit_NewValue, audit_user, audit_timestamp, audit_FieldName, audit_tablename)
VALUES ('UPDATE', @key, @OldValue, @NewValue, @User, getdate(), @FieldName, 'tbldetail')
END
END
IF UPDATE(intCodeID)
BEGIN
SET @OldValue = NULL
SET @NewValue = NULL
SET @intOldValue = 0
SET @intNewValue = 0
SET @FieldName ='intCodeID'
SET @intOldValue = (SELECT intCodeID FROM DELETED WHERE intDetailID = @key)
SET @intNewValue = (SELECT intCodeID FROM INSERTED WHERE intDetailID = @key)
IF (@intOldValue <> @intNewValue)
BEGIN
SET @OldValue = (SELECT vchCodeDescription FROM tblCode WHERE intCodeID = @intOldValue)
SET @NewValue = (SELECT vchCodeDescription FROM tblCode WHERE intCodeID = @intNewValue)
INSERT INTO tblAudit (audit_log_type, audit_key, audit_OldValue, audit_NewValue, audit_user, audit_timestamp, audit_FieldName, audit_tablename)
VALUES ('UPDATE', @key, @OldValue, @NewValue, @User, getdate(), @FieldName, 'tbldetail')
END
END
April 13, 2002 at 4:53 pm
The first thing I see is that you're assuming only one row is modified per trigger. You either have to use a cursor or convert your auditing code to do a set based operation (insert into... select from deleted vs insert into...values). I dont see anything obviously wrong with the code. Triggers can be hard to troubleshoot. Two methods I can offer. The first is to comment out almost every bit of code, run it and verify that all still works, uncomment a little more, try again, etc. The second is to create a couple tables to use in lieu of inserted/deleted so that you put in whatever data you want and test in Query Analyzer. One thing you might want to check is the permissions for the audit table.
I'm still experimenting with auditing, but overall I've been happiest with just logging the entire row to an audit table rather than worrying about what changed and what didnt.
Andy
April 14, 2002 at 9:13 am
Also I see you are doing a lot of work that is not needed with memory. Below I have posted what I would have done with the trigger that fixes some of the problems Andy mentions, especially if multiple items change at once, and also adds transactions (which I prefer to use), as well as, error messages that should help you find some answers about failures. Just a note, consider making audit_timestamp a NOT NULL column with a default of GETDATE() and remove from the trigger (means less code to troubleshoot in your trigger). Hope this helps.
CREATE TRIGGER TR_AuditDetail ON dbo.tblDetail
FOR UPDATE
AS
BEGIN TRANSACTION
IF UPDATE(intReference)
BEGIN
INSERT INTO
tblAudit
(
audit_log_type,
audit_key,
audit_OldValue,
audit_NewValue,
audit_user,
audit_timestamp,
audit_FieldName,
audit_tablename
)
SELECT
'UPDATE' audit_log_type,
i.intDetailID,
d.intReference,
i.intReference,
i.vchEditedBy,
GetDate(),
'intReference',
'tbldetail'
FROM
inserted i
INNER JOIN
deleted d
ON
i.intDetailID = d.intDetailID AND
i.intReference != d.intReference
IF @@error != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Insert Failure in intReference Audit code.',16,-1)
RETURN
END
END
IF UPDATE(intCodeID)
BEGIN
INSERT INTO
tblAudit
(
audit_log_type,
audit_key,
audit_OldValue,
audit_NewValue,
audit_user,
audit_timestamp,
audit_FieldName,
audit_tablename
)
SELECT
'UPDATE' audit_log_type,
i.intDetailID,
d.intReference,
i.intReference,
i.vchEditedBy,
GetDate(),
'intCodeID',
'tbldetail'
FROM
inserted i
INNER JOIN
deleted d
ON
i.intDetailID = d.intDetailID AND
i.intCodeID != d.intCodeID
IF @@error != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Insert Failure in intCodeID Audit code.',16,-1)
RETURN
END
END
COMMIT TRANSACTION
November 5, 2002 at 9:35 am
Antares686,
You wouldn't be able to post the table structure(generation script) for your tables used in the example below, would you?
November 5, 2002 at 6:40 pm
quote:
Antares686,You wouldn't be able to post the table structure(generation script) for your tables used in the example below, would you?
Sorry I was basing if I remember correctly on the example sackerdj gave us of what he was doing. But as you can see this was in April and I cannot remember if I did anything else.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply