Solved

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

Posted on 2008-11-03
3
310 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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