November 10, 2005 at 7:41 pm
I have a requirement that states that I need to capture an audit history on updates to records. I have chosen triggers to accomplish this. The implementation of this requirement is somewhat unique in that I only want to capture and store the columns that actually have changed (nulls or not). This will make the presentation much easier to comprehend. I am having trouble developing the trigger when multiple records are updated at once. It seems that I will have to create cursors for the inserted and deleted tables to handle an update. That just seems like alot of possibly unecessary code. Any help would be greatly appreciated.
Following is my source table schema:
CREATE TABLE [dbo].[tblProject] (
[ProjectId] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PENumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [smalldatetime] NULL ,
[LastUpdate] [smalldatetime] NULL ,
[UpdateBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectDescrip] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectObjectives] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TechChallenges] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TechApproach] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CapReqPayoff] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_PayOffPotential] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActiveProject] [bit] NOT NULL ,
[PocId] [int] NULL ,
[SBS1Id] [int] NULL ,
[SBS2Id] [int] NULL ,
[SBS3Id] [int] NULL ,
[TaskNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkPackageNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_CCTeam] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_Year] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FundingLeveNumberId] [int] NULL ,
[CheckedOut] [bit] NULL ,
[ProjectTypeId] [int] NULL ,
[PMOfficeId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Following is my auditing table schema:
CREATE TABLE [dbo].[tblProject] (
[AuditId] [bigint] IDENTITY (1, 1) NOT NULL ,
[ProjectId] [int] NOT NULL ,
[ProjectName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PENumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [smalldatetime] NULL ,
[LastUpdate] [datetime] NOT NULL ,
[UpdateBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProjectDescrip] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectObjectives] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TechChallenges] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TechApproach] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CapReqPayoff] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_PayOffPotential] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActiveProject] [bit] NULL ,
[PocId] [int] NULL ,
[SBS1Id] [int] NULL ,
[SBS2Id] [int] NULL ,
[SBS3Id] [int] NULL ,
[TaskNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkPackageNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_CCTeam] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Review_Year] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FundingLeveNumberId] [int] NULL ,
[CheckedOut] [bit] NULL ,
[ProjectTypeId] [int] NULL ,
[PMOfficeId] [int] NULL ,
[AuditType] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
November 11, 2005 at 11:12 am
You should not need to use a cursor. Just do :
'UPDATE <AuditTable>
SET <audittable.column name> = inserted.<column>
FROM <AuditTable>,Inserted
WHERE <AuditTable Key> = Inserted.<Key>
December 9, 2005 at 9:59 pm
/*
Triggers are not designed to work with multiple records at once. Batch processing will have to be accomplished using either @tables, #tempTables, or cursor, so that one record is inserted/updated at a time. I prefer @tables or #tempTables because it is easier to view the contents and determine if you're getting the results you want, but any of these methods will work.
Batch processing requires that you start with a table of inserts/updates so store those in a #tempTable with an ID column, retrieve the top 1 and store the ID in a variable. Execute the insert/update and delete the record with the matching ID/variable.
example:
#tempTable structure:
id, ProjectName, etc...
-- In Query Analyser
declare @ID = integer
while (select count(*) from #tempTable)>0
begin
select top 1 @ID = ID from #tempTable
update tblProject
set ProjectName = #tempTable.ProjectName,
NextColumn = #tempTable.NextColumn,
etc...
from tblProject, #tempTable
where tblProject.ProjectID = #tempTable.ProjectID
delete #tempTable where ID = @ID
end
Normally I would track changes to a single column in an audit table not try to capture all changes to a record at once, but here is an example of the trigger for tblProject based on what you've described. I am assuming that the audit table is not named tblProject so in my example I named it tblAudit.
I did not have time to test this so there may be errors but you get the idea. Hope this helps.
*/
Create Trigger trg_u_Audit_tblProject on tblProject
for update
as
if (select count(*) from inserted > 1
begin
raiserror('You cannot batch process against this table',16,1)
end
declare @ID as int
set @ID = 0
-- Setup the following for each field you want to monitor
/***********************************************************************************
Check to see if ProjectName is updated and the inserted value does not equal the deleted value
***********************************************************************************/
if update(ProjectName) and
if exists (select inserted.ProjectName
from inserted, deleted
where inserted.ProjectID = deleted.ProjectID
and isnull(inserted.ProjectName,'') <> isnull(deleted.ProjectID,''))
begin
-- Check if ProjectID exists in tblProject and that the ID field matches
if exists (select ProjectID
from tblAudit
where tblAudit.ProjectID = inserted.ProjectID
and tblAudit.AuditID = @ID)
begin
-- Update if match found
Update tblAudit
set ProjectName = inserted.ProjectName
from tblAudit, inserted
where tblAudit.ProjectID = inserted.ProjectID
and AuditID = @ID
end
else
begin
-- insert if match not found
Insert tblAudit (ProjectID,ProjectName)
select inserted.ProjectID, Inserted.ProjectName from Inserted
-- Find last inserted AuditID
select @ID = @@Identity from tblAudit
end
end
December 10, 2005 at 7:59 am
Fric frac, triggers are designed to work with multiple records at a time, as a matter of fact, that's why you have the inserted and deleted tablespaces. Set based operations should be applied to these whenever possible to ensure efficient execution of the trigger (which will cause the overall query to bog down otherwise).
Back to the original poster, your operation, just extracting the columns that change per row updated, will require a cursor or some similar procedural operation. You are performing separate and potentially different operations per row, so there's no real way around this. That is, unless you're controlling access through stored procedures and the like. If you are, it is probably better to put the auditing mechanism in the stored procedures so that you can simplify the auditing code.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply