• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

show only changed fields in audit generated by SQL Server 2005 After Update trigger

I use the attached trigger to form an audit trail of changes made to records in a project database.  How do I modify the routine to insert only changed values into the audit database and null values for the unchanged fields?
USE [DRDDB]
GO
/****** Object:  Trigger [dbo].[MENYSOWUpdateAudit]    Script Date: 11/03/2008 07:29:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[ProjectsUpdateAudit]
   ON  [dbo].[Projects] 
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
INSERT INTO dbo.ProjectsAudit([idProject]
      ,[Year]
      ,[Client]
      ,[Brand]
      ,[ProjectTitle]
      ,[MediaType]
      ,[MediaTypeDetail]
	,[LastChangedDate]
      ,[LastChangedBy]
	,[ReasonForChange])
SELECT 
[idProject]
	,[Year]
      ,[Client]
      ,[Brand]
      ,[ProjectTitle]
      ,[MediaType]
      ,[MediaTypeDetail]
	,[LastChangedDate]
      ,[LastChangedBy]
	,[ReasonForChange]
FROM DELETED
END

Open in new window

0
dvdillonla
Asked:
dvdillonla
1 Solution
 
Anthony PerkinsCommented:
This will give you the original value when changed (to get the new value switch the aliases):

ALTER TRIGGER [dbo].[ProjectsUpdateAudit]
   ON  [dbo].[Projects]
   AFTER UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

SET NOCOUNT ON;
 
INSERT      dbo.ProjectsAudit([idProject]
            ,[Year]
            ,[Client]
            ,[Brand]
            ,[ProjectTitle]
            ,[MediaType]
            ,[MediaTypeDetail]
            ,[LastChangedDate]
            ,[LastChangedBy]
            ,[ReasonForChange])
SELECT      d.[idProject]
            ,NULLIF(d.[Year], i.[Year])
            ,NULLIF(d.[Client], i.[Client])
            ,NULLIF(d.[Brand], i.[Brand])
            ,NULLIF(d.[ProjectTitle], i.[ProjectTitle])
            ,NULLIF(d.[MediaType], i.[MediaType])
            ,NULLIF(d.[MediaTypeDetail], i.[MediaTypeDetail])
            ,NULLIF(d.[LastChangedDate], i.[LastChangedDate])
            ,NULLIF(d.[LastChangedBy], i.[LastChangedBy])
            ,NULLIF(d.[ReasonForChange], i.[ReasonForChange])
FROM      DELETED d
            Inner Join Inserted i On d.idProject = i.idProject            -- Assuming that idProject is a primary key
END

0
 
dvdillonlaAuthor Commented:
Thank you, more elegant than the multiple case statements I got to work.  Much appreciated!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now