SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using a trigger


using a trigger

Author
Message
wendy elizabeth
wendy elizabeth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 772
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?
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7267 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6668 Visits: 7394
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" ;-)
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6668 Visits: 7394
Beat to the punch on this one :-D

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" ;-)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7267 Visits: 6431
MyDoggieJessie (1/22/2013)
Beat to the punch on this one :-D

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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7267 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7267 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4008 Visits: 5820
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() ?
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7267 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4008 Visits: 5820
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search