Generic Audit Trigger
-
I guess I'm just going to have to materialize this code and test it...
Would you post the CREATE TABLE statement for the two audit tables and the CREATE FUNCTION statement for the proc I sent you? I want to test it using the code that you've modified so that I'm testing the same thing. And, I'm on my way to work... won't be able to do anything on it until tonight. Thanks.
--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) -
Thanks Jeff.
I have included both audit table and the table the trigger is on. The UDF and Tally Table are exactly as you specified in your original email.
I have fixed the problem of the single field not firing the trigger - seems to have been some weird SQL Server problem. Once I deleted the column and re-recreated it, it worked fine.
The problem with the delte still stands. Seems to be something to do with the join to the TempInserted and TempDeleted tables.
CCB.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PickListItem_Audit]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[PickListItem_Audit]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuditDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AuditDetail]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuditHeader]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AuditHeader]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPickListItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPickListItem]
GO
CREATE TABLE [dbo].[AuditDetail] (
[AuditDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AuditHeaderID] [int] NULL ,
[FieldChanged] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OldValue] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL ,
[NewValue] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditHeader] (
[AuditHeaderID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ChangeType] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[TableName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[RecordID] [int] NULL ,
[ChangeDateTime] [datetime] NULL ,
[UserName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[UserPC] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Archived] [bit] NULL ,
[Batch] [varbinary] (16) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPickListItem] (
[intID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[intHeaderID] [int] NOT NULL ,
[strCode] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[strDescription] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[intOrder] [int] NULL ,
[intRecordID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AuditDetail] WITH NOCHECK ADD
CONSTRAINT [PK_AuditDetail] PRIMARY KEY CLUSTERED
(
[AuditDetailID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[AuditHeader] WITH NOCHECK ADD
CONSTRAINT [PK_AuditHeader] PRIMARY KEY CLUSTERED
(
[AuditHeaderID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[AuditHeader] ADD
CONSTRAINT [DF_AuditHeader_RecordID] DEFAULT (0) FOR [RecordID],
CONSTRAINT [DF_AuditHeader_ChangeDateTime] DEFAULT (getdate()) FOR [ChangeDateTime],
CONSTRAINT [DF_AuditHeader_UserName] DEFAULT (user_name()) FOR [UserName],
CONSTRAINT [DF_AuditHeader_UserPC] DEFAULT (host_name()) FOR [UserPC],
CONSTRAINT [DF_AuditHeader_Archived] DEFAULT (0) FOR [Archived]
GO
ALTER TABLE [dbo].[tblPickListItem] ADD
CONSTRAINT [DF_tblPickListItem_intOrder] DEFAULT (0) FOR [intOrder],
CONSTRAINT [DF_tblPickListItem_intRecordID] DEFAULT (0) FOR [intRecordID]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER PickListItem_Audit ON dbo.tblPickListItem
FOR INSERT, UPDATE, DELETE
AS
Set Nocount On -- Prevents unexpected feedback to GUI's and increases speed a bit --
-- Declare local variables --
Declare @TableName Varchar(50)
Set @TableName = 'tblPickListItem'
Declare @KeyField Varchar(50)
Declare @Act Char(6)
Declare @del Int
Declare @ins Int
Declare @ColumnsUpdated Varbinary(32) --1 byte for every 8 columns... 32 = 256 columns --
Declare @Batch Binary(16)
Set @Batch = NEWID()
Declare @RecordID Int
Declare @Counter Int
Declare @ColCount Int
Declare @RowNum Int
Declare @ColNum Int
Declare @ColName Varchar(50)
Declare @SQLString nVarchar(4000)
Declare @ParmDefinition nVarchar(4000)
-- Create a table to hold the names of the columns affected by the query. --
Create Table #ColNames
(
RowNum Int Primary Key Clustered,
ColNum Int,
ColName SysName --SysName is really NVarChar(128) --
)
-- Determine what type of query fired the trigger and populate temp tables for Inserted and Deleted records at the same time. --
Select * Into #TempDeleted From Deleted
Set @del = Sign(@@Rowcount)
Select * Into #TempInserted From Inserted
Set @ins = Sign(@@Rowcount)
Select @Act = Case
When @ins = 1 And @del = 0 Then 'Insert'
When @ins = 0 And @del = 1 Then 'Delete'
Else 'Update'
End
/* Create the correct headers for all records at the same time and identify which columns to audit based on query type.
Note that any given record will only be Inserted, Updated, or Deleted once per query, no matter what, so each RecordID in the trigger tables will be unique no matter what.
This eliminates the need for a cursor or a loop here... the Batch column is the key that will allow us to match the header and detail records. */
If @Act = 'Delete'
Begin
-- Setup to handle up to 256 columns (one "FF" for every 8 columns) --
Set @ColumnsUpdated = 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
Insert into AuditHeader
(
ChangeType,
TableName,
RecordID,
Batch
 
Select
ChangeType = @ACT,
TableName = @TableName,
RecordID = intID,
Batch = @Batch
From Deleted
End
Else -- @Act is 'Update' or 'Insert' --
Begin
Set @ColumnsUpdated = Columns_Updated()
Insert into AuditHeader
(
ChangeType,
TableName,
RecordID,
Batch
 
Select
ChangeType = @ACT,
TableName = @TableName,
RecordID = intID,
Batch = @Batch
From Inserted
End
-- At this point, we know enough to populate the col names table and capture the number of columns that were affected by the query --
Insert Into #ColNames
(
RowNum,
ColNum,
ColName)
Select
RowNum,
ColNum,
ColName
From dbo.udf_auditColumnsUpdated(@TableName, @ColumnsUpdated)
Set @ColCount = @@RowCount
/* No matter what type of query fired the trigger, we're ready for updating the Audit detail.
Because the dynamic SQL is so long, we still need to loop through for each column updated to make sure we don't run out of @SQLString variable space.
This should be a bit faster than a cursor. */
Set @Counter = 1 -- Assuming that SOMETHING fired the trigger at this point --
Set @ParmDefinition = N'@Batch VarBinary(16)'
While @Counter <= @ColCount
Begin
SELECT @SQLString =
N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue)
Select
h.AuditHeaderID,
FieldChanged = ''' + cn.ColName + ''',
OldValue = Left(Cast(d.'+ cn.ColName + ' as VarChar(8000)),4000),
NewValue = Left(Cast(i.'+ cn.ColName + ' as VarChar(8000)),4000)
From AuditHeader h
Left Outer Join #TempDeleted d On h.RecordID=d.intID
Left Outer Join #TempInserted i On h.RecordID=i.intID
Where h.Batch = @Batch'
From #ColNames cn
Where cn.RowNum = @Counter
Execute sp_ExecuteSQL @SQLString, @ParmDefinition, @Batch = @Batch
Set @Counter = @Counter + 1
End
-- Do a little housekeeping in case the same session fires the same trigger --
DROP TABLE #TempDeleted, #TempInserted, #ColNames
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Hmmmm.... I'm wondering if the error I got when I ran you build scripts might be a hint of your DELETE problem...
Warning: The table 'AuditDetail' has been created but its maximum row size (8085) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
--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) -
Ok... first, thank you very much for making my life easier for testing... the good news is, I get no errors with the following test code. The bad news is (also), I get no errors with the following test code...
INSERT INTO tblPickListItem
(
[intHeaderID],
[strCode],
[strDescription],
[intOrder],
[intRecordID]
)
SELECT 1,'TestRow1','Test Row 1',2,3 UNION ALL
SELECT 2,'TestRow2','Test Row 2',4,5
UPDATE tblPickListItem
SET IntOrder = 3
WHERE intHeaderID = 1
SELECT * FROM AuditDetail
DELETE tblPickListItem -- WHERE intHeaderID = 1
SELECT * FROM AuditDetail
SELECT * FROM AuditHeader
SELECT * FROM tblPickListItem
That means one of more of the following things could be true...
- You end up with rows greater than 8060 as the table creation advisory cautioned. In this case, the devil's in the data... not sure what to advise you there except to keep close track of the records you try to delete. DO YOU HAVE ANY COLUMNS WITH SPECIAL CHARACTERS OR SPACES IN THEM?
- One of the differences between your code and mine is that you didn't send me back the same function I sent you... you may have made an inadvertant change. I've attached the code again (below) just to be on the safe side. You should only change the name of the function... nothing else.
- The table you fixed isn't really fixed. I'd be tempted to blow it away and recreate it from scratch using the same code you just sent me (it obviously worked for me).
Here's the function code again...
CREATE FUNCTION dbo.fnColumnsUpdated
(@TableName SYSNAME, @BinaryColNums VARBINARY(128))
RETURNS @TableInfo TABLE (RowNum SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ColNum SMALLINT NULL, ColName VARCHAR(128) NULL)
AS
BEGIN --Begin the main body of the function
--===== Declare local variables
DECLARE @ByteCount TINYINT
SET @ByteCount = LEN(@BinaryColNums)
--===== Populate the return table with column numbers and names for the desired
--===== table according to the bit mask passed in through @BinaryColNums
INSERT INTO @TableInfo (ColNum,ColName)
SELECT c.ColNum, '['+COL_NAME(OBJECT_ID(@TableName), c.ColNum )+']' AS ColName
FROM (--Derived table "c" finds column numbers and names for columns identified
--in the @BinaryColNums variable usually provided by COLUMNS_UPDATED() in
--an INSERT or UPDATE table.
SELECT CASE ---- Determine which bit # is set and use a column number
WHEN BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1)
THEN ((ByteNum)*8)+t2.n --This creates the bit# that is set
ELSE 0
END AS ColNum
FROM (--Derived table splits the bytes of the binary number from left to
--right as outline in Books OnLine for use of COLUMNS_UPDATED().
SELECT n-1 AS ByteNum,
SUBSTRING(@BinaryColNums,N,1) AS BinaryVal
FROM dbo.TALLY t1 WITH (NOLOCK)
WHERE N<= @ByteCount
) b,
Tally t2 WITH (NOLOCK)
WHERE N<=8 --8 bits in each byte
AND BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1) --Suppress "0" bits
) c
--===== If @BinaryColNums indicated more columns than the are, delete the ColNums from the table
DELETE @TableInfo
WHERE ColName IS NULL
--===== Return the table and exit
RETURN
END --End of Function
--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) -
Hi Jeff, thanks for bearing with me.
You were right, I reduced the row length a bit and that solved the problem.
One final issue - It fails if the table the trigger is attached to has a text field and the text is modified or an insert or delete occurs - I think this is something to do with the Deleted and Inserted fields not able to have text columns in them - Any suggestions here ? If I can't catch the actual text changes, any idea how I could just insert an arbitary value to tell the user they were updated when updated and ignore then when inserting and deleting or if this is not possible, just ignore all text columns altogether.
The failing point is...
-- Determine what type of query fired the trigger and populate temp tables for Inserted and Deleted records at the same time. --
Select * Into #TempDeleted From Deleted
Set @del = Sign(@@Rowcount)
Select * Into #TempInserted From Inserted
Set @ins = Sign(@@Rowcount)
Thanks yet again !!!
CCB
-
Yep... First, I changed the function to capture the data type... again, you'll need to change the name of the function...
CREATE FUNCTION dbo.fnColumnsUpdated
(@TableName SYSNAME, @BinaryColNums VARBINARY(128))
RETURNS @TableInfo TABLE
(
RowNum SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ColNum SMALLINT NULL,
ColName VARCHAR(128) NULL,
ColType VARCHAR(128) NULL
)
AS
BEGIN --Begin the main body of the function
--===== Declare local variables
DECLARE @ByteCount TINYINT
SET @ByteCount = LEN(@BinaryColNums)
--===== Populate the return table with column numbers and names for the desired
--===== table according to the bit mask passed in through @BinaryColNums
INSERT INTO @TableInfo (ColNum,ColName)
SELECT c.ColNum, COL_NAME(OBJECT_ID(@TableName), c.ColNum ) AS ColName
FROM (--Derived table "c" finds column numbers and names for columns identified
--in the @BinaryColNums variable usually provided by COLUMNS_UPDATED() in
--an INSERT or UPDATE table.
SELECT CASE ---- Determine which bit # is set and use a column number
WHEN BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1)
THEN ((ByteNum)*8)+t2.n --This creates the bit# that is set
ELSE 0
END AS ColNum
FROM (--Derived table splits the bytes of the binary number from left to
--right as outline in Books OnLine for use of COLUMNS_UPDATED().
SELECT n-1 AS ByteNum,
SUBSTRING(@BinaryColNums,N,1) AS BinaryVal
FROM dbo.TALLY t1 WITH (NOLOCK)
WHERE N<= @ByteCount
) b,
Tally t2 WITH (NOLOCK)
WHERE N<=8 --8 bits in each byte
AND BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1) --Suppress "0" bits
) c
--===== If @BinaryColNums indicated more columns than the are, delete the ColNums from the table
DELETE @TableInfo
WHERE ColName IS NULL
--===== Get the data type for each column
UPDATE @TableInfo
SET ColType = sc.Data_Type
FROM @TableInfo ti,
INFORMATION_SCHEMA.COLUMNS sc WITH (NOLOCK)
WHERE sc.Table_Name = @TableName
AND sc.Column_Name = ti.ColName
--===== Return the table and exit
RETURN
END --End of Function
GO
To make this work without a performance hit, I had to remove the square brackets from around the column name that gets stored, as well. Just wanted you to be aware of that.
Second, you need to change a piece of the generic trigger code to mark Text, NText, and Image columns a bit differently...
While @Counter <= @ColCount
Begin
SELECT @SQLString =
N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue)
Select
h.AuditHeaderID,
FieldChanged = ''' + cn.ColName + ''',
OldValue = Case
When ''' + cn.ColType + ''' IN (''Text'',''NText'',''Image'')
Then ''<Text or Image changed>''
Else Left(Cast(d.'+ cn.ColName + ' as VarChar(8000)),4000)
End,
NewValue = Case
When ''' + cn.ColType + ''' IN (''Text'',''NText'',''Image'')
Then ''<Text or Image changed>''
Else Left(Cast(i.'+ cn.ColName + ' as VarChar(8000)),4000)
End
From AuditHeader h
Left Outer Join #TempDeleted d On h.RecordID=d.intID
Left Outer Join #TempInserted i On h.RecordID=i.intID
Where h.Batch = @Batch'
From #ColNames cn
Where cn.RowNum = @Counter
That should do it, Charlotte.
"Send beer, I already have enough pretzels..."
--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) -
Hi Jeff,
That works. The only problem I have now is that once I add a text column onto the end of the table the trigger is on to test,
The follwoing part of the trigger causes a problem...
Select * Into #TempDeleted From Deleted
Set @del = Sign(@@Rowcount)
Select * Into #TempInserted From Inserted
Set @ins = Sign(@@Rowcount)
It won't event compile and fails on editind data. It doesn't seem to like the fact that there is now a text field present. Any Idea how I can get around this?
Thanks Again
CCB
-
Heh-heh... My first inclination was to say "don't use text columns"
I won't even allow them into code at work because of problems like this on SQL Server 2000. I always make them store a filename for the text or image data.I gotta think about this one because I, as you can tell, just don't use those column types and haven't much experience with them in triggers.
--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) -
Hi Jeff,
I could use an Instead Of Trigger, then I could go back to the previous version of the UDF and trigger and I could actually audit the changes to the text fields. The only problem I then have is that I need to manually update the data in the live table as (The Instead of Trigger won't do this automatically). This is simple for Deletes - I can issue a
Delete A
From tblPickListItemTest
Join Deleted D On D.intID = A.intID
But If I try to insert or update with something like
Insert into tblName Select * from Inserted
it blows up because it does not like auto incrementing keys. Same problem with Updates - If I use dynamic code, it complains something about recurrsion...
Following is the dynamic stuff I was trying - added to the end of the trigger...
-- Need to do the physical Insert or Update or Delete because this is an Instead Of Trigger --
If @Act = 'Delete'
Begin
Delete A
From tblPickListItemTest A
Join #TempDeleted D On D.intID = A.intID
End
Else
Begin
If @Act = 'Update'
Begin
Set @Counter = 1 -- Assuming that SOMETHING fired the trigger at this point --
Set @SQLString = ''
While @Counter <= @ColCount
Begin
SELECT @SQLString = @SQLString + cn.ColName + '=' + 'I.' + cn.ColName +','
From #ColNames cn
Where cn.RowNum = @Counter and cn.ColName <> @KeyField
Set @Counter = @Counter + 1
End
Set @SQLString = N'Update A Set ' + Left(@SQLString, Len(@SQLString) - 1) + '
From #TempInserted As I
Join ' + @TableName + ' As A On A.' + @KeyField + '=I.' + @KeyField
Execute sp_ExecuteSQL @SQLString--, @ParmDefinition, @Batch = @Batch
End
Else -- Insert --
Begin
Set @Counter = 1 -- Assuming that SOMETHING fired the trigger at this point --
Set @SQLString = ''
While @Counter <= @ColCount
Begin
SELECT @SQLString = @SQLString + cn.ColName +','
From #ColNames cn
Where cn.RowNum = @Counter and cn.ColName <> @KeyField
Set @Counter = @Counter + 1
End
Set @SQLString = 'Insert Into ' + @TableName + ' (' + Left(@SQLString, Len(@SQLString) - 1) + ')
Select * From #TempInserted'
Execute sp_ExecuteSQL @SQLString--, @ParmDefinition, @Batch = @Batch
End
End
Thanks
CCB
-
Hi Jeff,
Its almost there...
When I add this to the end of the Instead Of Trigger...
-- Need to do the physical Insert or Update or Delete because this is an Instead Of Trigger --
If @Act = 'Delete'
Begin
Delete A
From tblPickListItemTest A
Join #TempDeleted D On D.intID = A.intID
End
Else
Begin
If @Act = 'Update'
Begin
Set @Counter = 1 -- Assuming that SOMETHING fired the trigger at this point --
Set @SQLString = ''
While @Counter <= @ColCount
Begin
SELECT @SQLString = @SQLString + ' A.' + cn.ColName + '=' + 'I.' + cn.ColName +','
From #ColNames cn
Where cn.RowNum = @Counter and cn.ColName <> @KeyField
Set @Counter = @Counter + 1
End
Set @SQLString = N'Update A Set' + Left(@SQLString, Len(@SQLString) - 1) + '
From ' + @TableName + ' As A
Join #TempInserted As I On I.' + @KeyField + '=A.' + @KeyField
Execute sp_ExecuteSQL @SQLString
/* Update A Set A.intHeaderID=I.intHeaderID, A.strCode=I.strCode, A.strDescription=I.strDescription, A.intOrder=I.intOrder, A.intRecordID=I.intRecordID, A.strRemarks=I.strRemarks
From tblPickListItemTest As A
Join Inserted As I On I.intID=A.intID
*/
End
Else -- Insert --
Begin
Set @Counter = 1 -- Assuming that SOMETHING fired the trigger at this point --
Set @SQLString = ''
While @Counter <= @ColCount
Begin
SELECT @SQLString = @SQLString + cn.ColName +','
From #ColNames cn
Where cn.RowNum = @Counter and cn.ColName <> @KeyField
Set @Counter = @Counter + 1
End
Declare @TempSelect varchar(8000)
Set @TempSelect = Left(@SQLString, Len(@SQLString) - 1)
Set @SQLString = 'Insert Into ' + @TableName + '(' + @TempSelect + ')
Select ' + @TempSelect + ' From #TempInserted'
Execute sp_ExecuteSQL @SQLString
/* Insert Into tblPickListItemTest(intHeaderID,strCode,strDescription,intOrder,intRecordID,strRemarks)
Select intHeaderID,strCode,strDescription,intOrder,intRecordID,strRemarks From Inserted
*/
End
End
It doesn't work. If I comment out the dynamic code and un-comment out the hard-coded code, everything works perfectly. The hardcoded code was originally generated by the dynamic code in Query Analyser. the only difference between the dynamic and the hardcoded is the Inserted table is referenced as opposed to the #TempInserted table.
When running with the dynamic code I get...
"Another user has modified the contents of this row or view. The database row you are modifying no longer exists in the database. INSTEAD OF triggers do not support direct recursion. Trigger execution failed" on an update and "INSTEAD OF triggers do not support direct recursion. Trigger execution failed" on an insert.
Does anyone have any idea on how to get around this ?
Thanks
CCB
-
I'm just about out of tricks on the Text columns, Charlotte... I'm still playing with it but haven't been able to come up with anything, yet.
--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) -
Jeff, you have been most helpful anyway, and I can getalong with what I have.
If I don't have the dynamic update to the actual table at the end of the Instead Of Trigger and I manually code it instead, it will work. It just means more work setting it up on every table and more maintenace issues in the future, but I can probably live with it.
What I don't understand is why the following works...
Update A Set A.intHeaderID=I.intHeaderID, A.strCode=I.strCode, A.strDescription=I.strDescription, A.intOrder=I.intOrder, A.intRecordID=I.intRecordID, A.strRemarks=I.strRemarks
From tblPickListItemTest As A
Join Inserted As I On I.intID=A.intID
And the foillowing doesn't...
Set @SQLString = " Update A Set A.intHeaderID=I.intHeaderID, A.strCode=I.strCode, A.strDescription=I.strDescription, A.intOrder=I.intOrder, A.intRecordID=I.intRecordID, A.strRemarks=I.strRemarks
From tblPickListItemTest As A
Join Inserted As I On I.intID=A.intID"
Execute sp_ExecuteSQL @SQLString
If I could solve this, It would all work.
CCB
-
It's because the Inserted table is only available in the current session... the dynamic SQL is actually a separate session with a different scope. That's the whole reason why we originally copied the Inserted and Deleted tables into temp tables... so the dynamic SQL could "see" what we needed it to see.
--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) -
I am working on implementing a modified version of this audit solution and while testing Jeff's function (fnColumnsUpdated) I discovered a bug when @BinaryColNums = 0x20 or any case where 20 is the last part (or if 20 is repeated like 0x202020) it would not work correctly. Which means that if you only update column #6 it would not register it (the same with column numbers 14, 22, 30, etc.) SQL's LEN function is not liking 0x20 but was fine for everything else that I tested. I replaced LEN with DATALENGTH and it works for every combination I've tested it for. I'm not sure why LEN was used anyway since it is a string function.
SET @ByteCount = LEN(@BinaryColNums)
Changed to:
SET @ByteCount = DATALENGTH(@BinaryColNums)
Jeff, this is a wonderful collection of code to buld an audit solution from! Thank you very much!
Kenney Hill
-
Thanks for the awesome compliment, Kenny... and thanks for the patch to the code!
--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)
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply