Solved

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

Posted on 2008-11-03
3
306 Views
Last Modified: 2010-04-21
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
Comment
Question by:dvdillonla
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 22891843
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
 

Author Closing Comment

by:dvdillonla
ID: 31512673
Thank you, more elegant than the multiple case statements I got to work.  Much appreciated!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now