Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using a trigger Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 8:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 366, Visits: 428
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?
Post #1410313
Posted Tuesday, January 22, 2013 9:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,641, Visits: 5,289
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1410329
Posted Tuesday, January 22, 2013 9:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
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 @ErrNo varchar(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 @ErrNo varchar(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; They'll drag you down to their level and beat you with experience"
Post #1410331
Posted Tuesday, January 22, 2013 9:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1410332
Posted Tuesday, January 22, 2013 9:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,641, Visits: 5,289
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1410333
Posted Tuesday, January 22, 2013 10:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,641, Visits: 5,289
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1410340
Posted Tuesday, January 22, 2013 11:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,641, Visits: 5,289
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1410344
Posted Wednesday, January 23, 2013 3:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 1,948, Visits: 3,221
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() ?
Post #1410446
Posted Wednesday, January 23, 2013 4:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,641, Visits: 5,289
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1410475
Posted Wednesday, January 23, 2013 5:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 1,948, Visits: 3,221
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
Post #1410503
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse