January 22, 2013 at 8:02 pm
In a C# 2010 desktop application, I need to add an audit table to an application that would be similar to the 'main' table. Basically whenever anyting is changed in the 'main' table I need to show the corresponding change in the 'audit' table. The 'audit' table will be a mirror copy of the 'main' table. The only difference is one table is called 'main' table and the other table is called the 'audit' table.
**Note these table reside in a sql server 2008 r2 database.
Would you setup a trigger? If so, can you show me the sql you would use?
What sql would you use?
January 22, 2013 at 9:19 pm
Hi Wendy!
I'll give you a general answer and if you'd like a more specific one post DDL for the main table and I (or someone else) will see what we can do.
Yes a trigger can be used but there's also an advanced auditing feature available in SQL Server which I can't offhand remember the name of. Personally I wouldn't set up the audit table to be a mirror of the main table because you'll constantly be storing lots of data that hasn't changed.
Instead, I'd set up a table with these fields:
- Table name
- Column name (that changed)
- Date/time of change
- User making the change
- Old value
- New value
Old and new values could be a large VARCHAR (or NVARCHAR) that covers the largest width field you've got to deal with.
In the UPDATE trigger, you can use the 2 pseudo tables INSERTED and DELETED to identify what fields have changed and then use CROSS APPLY VALUES to UNPIVOT (see the first article in my signature links for details on this) the changed fields into the Old and New Value columns in the audit table. Don't forget that when you write the trigger, a bulk update (multiple rows) returns multiple rows in the pseudo tables, and the trigger only fires once for each SQL UPDATE (or MERGE). You'll also need to remember to CAST or CONVERT certain field types to the VARCHAR format you want (like for DATETIME to make it easier to unravel later).
All that sounds complicated maybe, but it is really quite simple once you see it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 9:46 pm
You have 2 options, forgive my quick code but wanted to throw something together that would work for you (I tested it locally just fine) - hope this helps!
1) Re-execute the original UPDATE statement against both tables, except the second time would be against the audit table
2) Use a trigger (after update). In the audit table I'd high recommend you add some neat fields to track the changes...say perhaps: CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, PriorValue
First trigger (for the original inserts)
CREATE TRIGGER [dbo].[tr_NewRecord] ON [dbo].[TriggerTest]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrNovarchar(15), @ErrMsg varchar(2000)
DECLARE @Subject varchar(500), @Body varchar(500)
DECLARE @To varchar(150), @Bcc varchar(150)
BEGIN TRY
INSERT INTO dbo.TriggerTestAudit (ID, FName, LName)
SELECT ID, FName, LName FROM INSERTED
END TRY
BEGIN CATCH
SELECT
@ErrNo = ERROR_NUMBER(),
@ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = ' :: ERROR :: ' + @ErrNo + ' <<Add Custom Msg here>>'
SET @Subject = CAST(@@SERVERNAME AS varchar) + @ErrMsg
SET @Body = '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + @ErrMsg + CHAR(13)
+ '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + '<<Enter additional your blurb here>>' + CHAR(13) + CHAR(13)
+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc, @subject = @Subject, @body = @Body, @importance = 'High'
END CATCH
END
Test.
Second Trigger (for the updates)CREATE TRIGGER [dbo].[tr_RecordUpdated] ON [dbo].[TriggerTest]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrNovarchar(15), @ErrMsg varchar(2000)
DECLARE @Subject varchar(500), @Body varchar(500)
DECLARE @To varchar(150), @Bcc varchar(150)
BEGIN TRY
UPDATE A
SET ID = X.ID,
FName = X.FName,
LName = X.LName
FROM dbo.TriggerTestAudit A
INNER JOIN INSERTED X ON a.ID = X.ID
WHERE A.ID = X.ID
END TRY
BEGIN CATCH
SELECT
@ErrNo = ERROR_NUMBER(),
@ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = ' :: ERROR :: ' + @ErrNo + ' <<Add Custom Msg here>>'
SET @Subject = CAST(@@SERVERNAME AS varchar) + @ErrMsg
SET @Body = '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + @ErrMsg + CHAR(13)
+ '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + '<<Enter additional your blurb here>>' + CHAR(13) + CHAR(13)
+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc, @subject = @Subject, @body = @Body, @importance = 'High'
END CATCH
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 22, 2013 at 9:47 pm
Beat to the punch on this one 😀
Dwain has made some very valid points!
@Dwain, are you referring to CDC (Change Data Capture)?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 22, 2013 at 9:50 pm
MyDoggieJessie (1/22/2013)
Beat to the punch on this one 😀Dwain has made some very valid points!
@Dwain, are you referring to CDC (Change Data Capture)?
Yeah! CDC is it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 10:13 pm
MDJ - That wasn't exactly what I had in mind. More like this (ignoring the error handling in yours which I recommend anyway to be safe):
CREATE TABLE Main
(ID INT IDENTITY PRIMARY KEY
,C1 DATETIME
,C2 VARCHAR(10)
,C3 INT
,UserID VARCHAR(20)) -- Last user who updated the record
GO
CREATE TABLE Audit
(TableName VARCHAR(20)
,ColumnName VARCHAR(20)
,RecordID INT
,UserID VARCHAR(20)
,UpdatedDate VARCHAR(10)
,OldValue VARCHAR(100)
,NewValue VARCHAR(100))
GO
CREATE TRIGGER [dbo].[tr_Audit] ON Main
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Audit
SELECT '#Main', ColName, CASE WHEN i.ID IS NULL THEN d.ID ELSE i.ID END
,CASE WHEN i.UserID IS NULL THEN d.UserID ELSE i.UserID END
,GETDATE(), OldValue, NewValue
FROM INSERTED i
FULL OUTER JOIN DELETED d ON i.ID = d.ID
CROSS APPLY (
VALUES ('C1', CONVERT(VARCHAR(27), d.C1, 113), CONVERT(VARCHAR(27), i.C1, 113))
,('C2', d.C2, i.C2)
,('C3', CAST(d.C3 AS VARCHAR(20)), CAST(i.C3 AS VARCHAR(20)))
) a(ColName, OldValue, NewValue)
-- You may want to refine this WHERE just a bit.
WHERE ISNULL(OldValue,'') <> ISNULL(NewValue,'')
END
GO
INSERT INTO Main
SELECT GETDATE()-1, 'A VALUE', 42, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'B VALUE', 40, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'C VALUE', 40, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'D VALUE', 40, 'Dwain.C'
UPDATE a
SET C2='X VALUE', UserID='MyDoggieJessie'
FROM Main a
WHERE ID IN (2, 3)
DELETE FROM Main
WHERE ID = 4
-- Add an update to the audit trail on any DELETE
-- to capture the deleting user
UPDATE Audit SET UserID = 'MyCatHobbsy'
WHERE RecordID = 4 AND NewValue IS NULL
SELECT * FROM Main
SELECT * FROM Audit
DROP TRIGGER [dbo].[tr_Audit]
DROP TABLE Main
DROP TABLE Audit
Note that I forgot to include the primary key value in the list of fields to store in my original post.
Notice how the trigger workds for INSERTs, UPDATEs and DELETEs. It is possible you don't want to log all of these events, so for example:
1. INSERT and UPDATE only - Use a LEFT JOIN of INSERTED to DELETED (INSERTED will always have rows but DELETED will not on INSERT).
2. UPDATE and DELETE only - use a LEFT JOIN of DELETED to INSERTED (DELETED will always have rows but INSERTED will not on DELETE).
etc.
Edit: Silly me! Forgot the WHERE clause in the trigger.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 11:03 pm
Upon reflection, I realized that capturing the UserID that deleted the record is not possible in the TRIGGER itself.
So I went back and modified the code to show you how it could be done. I don't particularly like it but it works and maybe someone has a better idea.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 3:36 am
dwain.c (1/22/2013)
Upon reflection, I realized that capturing the UserID that deleted the record is not possible in the TRIGGER itself.So I went back and modified the code to show you how it could be done. I don't particularly like it but it works and maybe someone has a better idea.
SUSER_SNAME() / ORIGINAL_LOGIN() ?
January 23, 2013 at 4:38 am
Gazareth (1/23/2013)
dwain.c (1/22/2013)
Upon reflection, I realized that capturing the UserID that deleted the record is not possible in the TRIGGER itself.So I went back and modified the code to show you how it could be done. I don't particularly like it but it works and maybe someone has a better idea.
SUSER_SNAME() / ORIGINAL_LOGIN() ?
As those would be associated with SQL Server, they may bear no relation to the application login, which is most likely what you'd want to capture.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 5:31 am
dwain.c (1/23/2013)
Gazareth (1/23/2013)
dwain.c (1/22/2013)
Upon reflection, I realized that capturing the UserID that deleted the record is not possible in the TRIGGER itself.So I went back and modified the code to show you how it could be done. I don't particularly like it but it works and maybe someone has a better idea.
SUSER_SNAME() / ORIGINAL_LOGIN() ?
As those would be associated with SQL Server, they may bear no relation to the application login, which is most likely what you'd want to capture.
True, but they might do. Just offering a suggestion 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply