rocky050371
asked on
Trigger Audit Help
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)
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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.