June 29, 2011 at 6:52 pm
Hi,
I've discovered a bug in the triggers that are used to gather auditing information on what changes on updates to the tables in our 2005 database. I hasten to add I didn't write them, so please don't berate me for the way they were written, although I'm wholly receptive to suggestions as to any better ways.
Basically we have four columns all varchar(254), null which are detected as changed and show up in the audit xml as changed but they haven't changed. This problem only happens when the values before and after are both empty strings. If they change from fred to george or fred to empty string, or empty string to fred they show up as changed correctly.
We have another column whose properties are EXACTLY the same, i.e. varchar(254) null, plus all the other properties on the column including the Enhanced ones (which there aren't any) on the working or the non working column.
There are no constraints involving or default values set up on these columns that we thought could cause this problem. There are varchars in the same table but smaller, 70 is one size, and those work fine.
So we're confused...
ALTER TRIGGER [dbo].[myTable]
ON [dbo].[myTable]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @auditXML as varchar(6000)
DECLARE @auditNode as varchar(6000)
DECLARE @actionType as bit
DECLARE @ACTIONTYPEINSERT as bit
DECLARE @ACTIONTYPEUPDATE as bit
DECLARE @before as varchar(254)
DECLARE @after as varchar(254)
DECLARE @Num as int
DECLARE @iTrancount as int
SELECT @iTrancount = @@trancount
IF (@iTrancount = 0)
BEGIN TRAN
SET @auditXML = ''
SET @auditNode = ''
IF NOT EXISTS(SELECT * FROM deleted)
SET @actionType = @ACTIONTYPEINSERT
ELSE
SET @actionType = @ACTIONTYPEUPDATE
SET @before = ''
SET @after = ''
// Sample code to detect a change on the columns in the table
IF @actionType <> @ACTIONTYPEUPDATE
OR NOT EXISTS (SELECT Description
FROM deleted
WHERE Description
IN(SELECT Description FROM inserted))
BEGIN
SELECT @before=Act FROM deleted
SELECT @after=Act FROM inserted
SELECT @auditNode = dbo.CreateAuditXML('Act', @before, @after) ==> see below for function
IF (LEN(@auditXML) + LEN(@auditNode)) <= 6000
BEGIN
SET @auditXML = @auditXML + @auditNode
END
END
===> The same as above for each column
-- Insert statements for trigger here
INSERT INTO CtlAud (Num, Alp, TblRowNum, Act, Des, EntDte, EntByNum)
SELECT @Num, 'AccTyp', Num, 1, @auditXML, GetDate(), ModByNum
FROM inserted
IF @iTrancount = 0
COMMIT TRAN
END
ALTER FUNCTION [dbo].[CreateAuditXML]
(
-- Add the parameters for the function here
@Code varchar(40),
@before varchar(254),
@after varchar(254)
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @AuditXML varchar(3000)
-- Add the T-SQL statements to compute the return value here
SELECT @AuditXML = '<ITEM><CODE>' + @Code + '</CODE><BEFOREVALUE>' + ISNULL(@before, '') + '</BEFOREVALUE>' +
'<AFTERVALUE>' + ISNULL(@after, '') + '</AFTERVALUE></ITEM>'
-- Return the result of the function
RETURN @AuditXML
END
Any help would be most gratefully received.
Kind Regards
Colin Basterfield
June 30, 2011 at 1:50 pm
The problem is that this will never work for an insert. This is checking if @actionType <> @ACTIONTYPEUPDATE. @ACTIONTYPEUPDATE is declared as a bit but is never set. Thus it equals null. In the example below I stripped a lot of stuff not surrounding the issue. Notice that for a delete it will work because of the OR NOT EXISTS...
DECLARE @actionType as bit
DECLARE @ACTIONTYPEINSERT as bit
DECLARE @ACTIONTYPEUPDATE as bit
--IF NOT EXISTS(SELECT * FROM deleted)
SET @actionType = @ACTIONTYPEINSERT
--ELSE
--SET @actionType = @ACTIONTYPEUPDATE
select @actionType, @ACTIONTYPEUPDATE
--// Sample code to detect a change on the columns in the table
IF @actionType <> @ACTIONTYPEUPDATE
--OR NOT EXISTS (SELECT Description FROM deleted WHERE Description
--IN(SELECT Description FROM inserted))
BEGIN
SELECT 'RUT ROH'
END
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2011 at 2:03 pm
I think his problem runs far deeper than that.. If he changes more than one row there is no way to tell which one will be logged. and while there may be the assumption that the code will only change one record you cannot guarantee that or make that assumption here.
Also @NUM does what?
Also, this code does not actually check to see IF there was a change, at least not effectively, the IN statement will fail on NULL values. Also if you are joining inserted and updated you can join on the table key for updates. what is the key fields for this table?
Altogether this sproc is quite a mess, and no, I'm not trying to beat you up.. I think we'll need to more or less start over, and I can help you with that.
CEWII
June 30, 2011 at 2:06 pm
Good point Elliot. It certainly a train wreck and the rewrite would not only be legible, it would be functional too.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply