Solved

Trigger Audit Help

Posted on 2010-09-08
1
269 Views
Last Modified: 2012-05-10
I have the following I have adapted from a web example, the problem I have is that I need to store both the new and old value for the updates, in additionI also want to add to the audit table, recordid, name etc from the original table. Any help would be good

USE [GeminiSQL_v8]
GO
/****** Object:  Trigger [dbo].[Audit_Trigger]    Script Date: 09/08/2010 16:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Audit_Trigger]
ON [dbo].[Cashbook]
FOR UPDATE,DELETE
AS
Declare @OldMessage varchar(200)

IF (SELECT COUNT(*) FROM inserted) > 0
      begin
            set @OldMessage = (SELECT
            (case
            when update(Date)
            then deleted.Date
            end) as Date,
            (case when update(Credit)
            then deleted.Credit
            end) as Credit from deleted
            inner join Cashbook
            on deleted.Reference=Cashbook.Reference
            FOR XML AUTO)
      end
else
      BEGIN
            SET @OldMessage = (SELECT Date ,Credit
            from deleted
            FOR XML AUTO)
end

insert into Audit(Type, TableName, NewValue) values (1, 'Customer', @OldMessage)
0
Comment
Question by:rocky050371
1 Comment
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 33631698
Hi,

Triggers have two virtual tables inserted and deleted.

That is, for an update, both inserted and deleted tables exist and are populated with old (deleted) and new (inserted) values.

Regards
  David
IF (SELECT COUNT(*) FROM inserted) > 0 

      begin

            set @OldMessage = (SELECT 

            (case

            when update(Date)

            then deleted.Date

            end) as DDate,

            (case when update(Credit)

            then deleted.Credit

            end) as DCredit

		, (case

		when updated( Date )

		then inserted.Date 

		end ) as IDate 

		, (case

		when updated( Credit )

		then inserted.Credit 

		end ) as ICredit 

            from deleted 

		inner join inserted

			on inserted.Reference = deleted.Reference

            inner join Cashbook

            on deleted.Reference=Cashbook.Reference 

            FOR XML AUTO)

      end

else

      BEGIN

            SET @OldMessage = (SELECT Date ,Credit

            from deleted 

            FOR XML AUTO)

end

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

20 Experts available now in Live!

Get 1:1 Help Now