November 11, 2005 at 8:03 am
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 1:29 pm
CREATE TRIGGER TR_PROJECT_UPDATE ON [dbo].[tblProject]
FOR UPDATE
INSERT INTO [dbo].[tblProject_audit]
(Field1,field2,....)
SELECT
CASE
ISNULL(INSERTED.FIELD1,unplausiblevalue)=ISNULL(DELETED.FIELD1,unplausiblevalue) THEN unplausiblevalue /*not changed*/
ELSE DELETED.FIELD1 /*old value*/
END AS FIELD 1
,
CASE
...
FROM INSERTED INNER JOIN DELETED ON INSERTED.PK=DELETED.PK
WHERE ISNULL(INSERTED.FIELD1,unplausiblevalue)<>ISNULL(DELETED.FIELD1,unplausiblevalue)
OR ...
you can repeat this if you want to store the new value also
November 11, 2005 at 1:44 pm
Thanks for your reply! Does your solution handle the fact that if no changes are made to the targeted columns, although the update if fired, an insert won't be made to the audit table? I have included the code I currently have (not yet completely tested) to give you an idea of what I was thinking and how big an idiot I am when it comes to SQL.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER TR_PROJECT ON tblProject
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
--Miscellaneous variables
DECLARE @changeNeeded int
-- old variables
DECLARE @oldProjectId int
DECLARE @oldProjectName nvarchar(100)
DECLARE @oldProjectNumber nvarchar(20)
DECLARE @oldPENumber nvarchar(20)
DECLARE @oldDateEntered smalldatetime
DECLARE @oldLastUpdate smalldatetime
DECLARE @oldUpdateBy nvarchar(50)
DECLARE @oldProjectDescrip nvarchar(750)
DECLARE @oldProjectObjectives nvarchar(750)
--DECLARE @oldTechChallenges ntext
--DECLARE @oldTechApproach ntext
--DECLARE @oldCapReqPayoff ntext
DECLARE @oldReview_PayOffPotential nvarchar(7)
DECLARE @oldActiveProject bit
DECLARE @oldPocId int
DECLARE @oldSBS1Id int
DECLARE @oldSBS2Id int
DECLARE @oldSBS3Id int
DECLARE @oldTaskNumber nvarchar(20)
DECLARE @oldWorkPackageNumber nvarchar(20)
DECLARE @oldReview_CCTeam nvarchar(50)
DECLARE @oldReview_Year nvarchar(4)
DECLARE @oldFundingLeveNumberId int
DECLARE @oldCheckedOut bit
DECLARE @oldProjectTypeId int
DECLARE @oldPMOfficeId int
-- new variables
DECLARE @newProjectId int
DECLARE @newProjectName nvarchar(100)
DECLARE @newProjectNumber nvarchar(20)
DECLARE @newPENumber nvarchar(20)
DECLARE @newDateEntered smalldatetime
DECLARE @newLastUpdate smalldatetime
DECLARE @newUpdateBy nvarchar(50)
DECLARE @newProjectDescrip nvarchar(750)
DECLARE @newProjectObjectives nvarchar(750)
--DECLARE @newTechChallenges ntext
--DECLARE @newTechApproach ntext
--DECLARE @newCapReqPayoff ntext
DECLARE @newReview_PayOffPotential nvarchar(7)
DECLARE @newActiveProject bit
DECLARE @newPocId int
DECLARE @newSBS1Id int
DECLARE @newSBS2Id int
DECLARE @newSBS3Id int
DECLARE @newTaskNumber nvarchar(20)
DECLARE @newWorkPackageNumber nvarchar(20)
DECLARE @newReview_CCTeam nvarchar(50)
DECLARE @newReview_Year nvarchar(4)
DECLARE @newFundingLeveNumberId int
DECLARE @newCheckedOut bit
DECLARE @newProjectTypeId int
DECLARE @newPMOfficeId int
Set @changeNeeded = 0
Declare CursorIn Cursor for
SELECT
D.ProjectId as oldProjectId,
D.ProjectName as oldProjectName,
D.ProjectNumber as oldProjectNumber,
D.PENumber as oldPENumber,
D.DateEntered as oldDateEntered,
D.LastUpdate as oldLastUpdate,
D.UpdateBy as oldUpdateBy,
D.ProjectDescrip as oldProjectDescrip,
D.ProjectObjectives as oldProjectObjectives,
-- D.TechChallenges as oldTechChallenges,
-- D.TechApproach as oldTechApproach,
-- D.CapReqPayoff as oldCapReqPayoff,
D.Review_PayOffPotential as oldReview_PayOffPotential,
D.ActiveProject as oldActiveProject,
D.PocId as oldPocId,
D.SBS1Id as oldSBS1Id,
D.SBS2Id as oldSBS2Id,
D.SBS3Id as oldSBS3Id,
D.TaskNumber as oldTaskNumber,
D.WorkPackageNumber as oldWorkPackageNumber,
D.Review_CCTeam as oldReview_CCTeam,
D.Review_Year as oldReview_Year,
D.FundingLeveNumberId as oldFundingLeveNumberId,
D.CheckedOut as oldCheckedOut,
D.ProjectTypeId as oldProjectTypeId,
D.PMOfficeId as oldPMOfficeId,
I.ProjectId as newProjectId,
I.ProjectName as newProjectName,
I.ProjectNumber as newProjectNumber,
I.PENumber as newPENumber,
I.DateEntered as newDateEntered,
I.LastUpdate as newLastUpdate,
I.UpdateBy as newUpdateBy,
I.ProjectDescrip as newProjectDescrip,
I.ProjectObjectives as newProjectObjectives,
-- I.TechChallenges as newTechChallenges,
-- I.TechApproach as newTechApproach,
-- I.CapReqPayoff as newCapReqPayoff,
I.Review_PayOffPotential as newReview_PayOffPotential,
I.ActiveProject as newActiveProject,
I.PocId as newPocId,
I.SBS1Id as newSBS1Id,
I.SBS2Id as newSBS2Id,
I.SBS3Id as newSBS3Id,
I.TaskNumber as newTaskNumber,
I.WorkPackageNumber as newWorkPackageNumber,
I.Review_CCTeam as newReview_CCTeam,
I.Review_Year as newReview_Year,
I.FundingLeveNumberId as newFundingLeveNumberId,
I.CheckedOut as newCheckedOut,
I.ProjectTypeId as newProjectTypeId,
I.PMOfficeId as newPMOfficeId
FROM deleted D inner join inserted I on D.ProjectId = I.ProjectId
Open CursorIn
If @@CURSOR_ROWS > 0
Begin
Fetch CursorIn Into
@oldProjectId,
@oldProjectName,
@oldProjectNumber,
@oldPENumber,
@oldDateEntered,
@oldLastUpdate,
@oldUpdateBy,
@oldProjectDescrip,
@oldProjectObjectives,
-- @oldTechChallenges,
-- @oldTechApproach,
-- @oldCapReqPayoff,
@oldReview_PayOffPotential,
@oldActiveProject,
@oldPocId,
@oldSBS1Id,
@oldSBS2Id,
@oldSBS3Id,
@oldTaskNumber,
@oldWorkPackageNumber,
@oldReview_CCTeam,
@oldReview_Year,
@oldFundingLeveNumberId,
@oldCheckedOut,
@oldProjectTypeId,
@oldPMOfficeId,
@newProjectId,
@newProjectName,
@newProjectNumber,
@newPENumber,
@newDateEntered,
@newLastUpdate,
@newUpdateBy,
@newProjectDescrip,
@newProjectObjectives,
-- @newTechChallenges,
-- @newTechApproach,
-- @newCapReqPayoff,
@newReview_PayOffPotential,
@newActiveProject,
@newPocId,
@newSBS1Id,
@newSBS2Id,
@newSBS3Id,
@newTaskNumber,
@newWorkPackageNumber,
@newReview_CCTeam,
@newReview_Year,
@newFundingLeveNumberId,
@newCheckedOut,
@newProjectTypeId,
@newPMOfficeId
While (@@FETCH_STATUS = 0)
Begin
if @newProjectDescrip <> @oldProjectDescrip
BEGIN
set @changeNeeded = 1
END
else
BEGIN
set @oldProjectDescrip = null
END
if @newProjectName <> @oldProjectName
BEGIN
set @changeNeeded = 1
END
else
BEGIN
set @oldProjectName = null
END
--repeat for each column...
--Is a change actually requested? If not, don't fire update, otherwise do.
if @changeNeeded = 1
BEGIN
Insert TATMChangeHistory.dbo.tblProject
(AuditType,
ProjectId,
ProjectName,
ProjectDescrip,
UpdateBy)
values ( 2,
@oldProjectId,
@oldProjectName,
@oldProjectDescrip,
@newupdateBy)
if @@ERROR > 0
BEGIN
RAISERROR('Update trigger failed' ,16,1)
ROLLBACK TRAN
END
--Proceed with the update
BEGIN
Update tblProject
set ProjectName = @newProjectName,
ProjectNumber = @newProjectNumber,
PENumber = @newPENumber,
DateEntered = @newDateEntered,
LastUpdate = @newLastUpdate,
UpdateBy = @newUpdateBy,
ProjectDescrip = @newProjectDescrip,
ProjectObjectives = @newProjectObjectives,
--TechChallenges = @newTechChallenges,
--TechApproach = @newTechApproach,
--CapReqPayoff = @newCapReqPayoff,
Review_PayOffPotential = @newReview_PayOffPotential,
ActiveProject = @newActiveProject,
PocId = @newPocId,
SBS1Id = @newSBS1Id,
SBS2Id = @newSBS2Id,
SBS3Id = @newSBS3Id,
TaskNumber = @newTaskNumber,
WorkPackageNumber = @newWorkPackageNumber,
Review_CCTeam = @newReview_CCTeam,
Review_Year = @newReview_Year,
FundingLeveNumberId = @newFundingLeveNumberId,
CheckedOut = @newCheckedOut,
ProjectTypeId = @newProjectTypeId,
PMOfficeId = @newPMOfficeId
where ProjectId = @newProjectId
END
Fetch CursorIn Into
@oldProjectId,
@oldProjectName,
@oldProjectNumber,
@oldPENumber,
@oldDateEntered,
@oldLastUpdate,
@oldUpdateBy,
@oldProjectDescrip,
@oldProjectObjectives,
-- @oldTechChallenges,
-- @oldTechApproach,
-- @oldCapReqPayoff,
@oldReview_PayOffPotential,
@oldActiveProject,
@oldPocId,
@oldSBS1Id,
@oldSBS2Id,
@oldSBS3Id,
@oldTaskNumber,
@oldWorkPackageNumber,
@oldReview_CCTeam,
@oldReview_Year,
@oldFundingLeveNumberId,
@oldCheckedOut,
@oldProjectTypeId,
@oldPMOfficeId,
@newProjectId,
@newProjectName,
@newProjectNumber,
@newPENumber,
@newDateEntered,
@newLastUpdate,
@newUpdateBy,
@newProjectDescrip,
@newProjectObjectives,
-- @newTechChallenges,
-- @newTechApproach,
-- @newCapReqPayoff,
@newReview_PayOffPotential,
@newActiveProject,
@newPocId,
@newSBS1Id,
@newSBS2Id,
@newSBS3Id,
@newTaskNumber,
@newWorkPackageNumber,
@newReview_CCTeam,
@newReview_Year,
@newFundingLeveNumberId,
@newCheckedOut,
@newProjectTypeId,
@newPMOfficeId
END
End
End
Close CursorIn
Deallocate CursorIn
--UPDATE tblProject
SET
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
November 11, 2005 at 6:39 pm
You're welcome.
1)the where clause checks if there was a change present for the given row
WHERE ISNULL(INSERTED.FIELD1,unplausiblevalue)<>ISNULL(DELETED.FIELD1,unplausiblevalue)
OR
ISNULL(INSERTED.FIELD2,unplausiblevalue)<>ISNULL(DELETED.FIELD2,unplausiblevalue)
... (every column)
2)as far as I know a trigger is always a transaction, so if there is any error it will rollback the whole set
3) consider cursors as last option (or almost), many cursors can be converted to set based solutions with the bonus of huge performance gains.
November 13, 2005 at 3:58 pm
If you want to kill server performance, increase response time dramatically, make the number of timeout errors unacceptable and finally get fired you definetely should use cursors in triggers.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply