InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:35:56 GMT20http://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspx[quote][b]dwain.c (1/23/2013)[/b][hr][quote][b]Gazareth (1/23/2013)[/b][hr][quote][b]dwain.c (1/22/2013)[/b][hr]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.[/quote]SUSER_SNAME() / ORIGINAL_LOGIN() ?[/quote]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.[/quote]True, but they might do. Just offering a suggestion :-)Wed, 23 Jan 2013 05:31:37 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspx[quote][b]Gazareth (1/23/2013)[/b][hr][quote][b]dwain.c (1/22/2013)[/b][hr]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.[/quote]SUSER_SNAME() / ORIGINAL_LOGIN() ?[/quote]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.Wed, 23 Jan 2013 04:38:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspx[quote][b]dwain.c (1/22/2013)[/b][hr]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.[/quote]SUSER_SNAME() / ORIGINAL_LOGIN() ?Wed, 23 Jan 2013 03:36:32 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxUpon 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.Tue, 22 Jan 2013 23:03:19 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxMDJ - 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):[code="sql"]CREATE TABLE Main (ID INT IDENTITY PRIMARY KEY ,C1 DATETIME ,C2 VARCHAR(10) ,C3 INT ,UserID VARCHAR(20)) -- Last user who updated the recordGOCREATE TABLE Audit (TableName VARCHAR(20) ,ColumnName VARCHAR(20) ,RecordID INT ,UserID VARCHAR(20) ,UpdatedDate VARCHAR(10) ,OldValue VARCHAR(100) ,NewValue VARCHAR(100))GOCREATE TRIGGER [dbo].[tr_Audit] ON Main AFTER INSERT, UPDATE, DELETEASBEGINSET 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,'')ENDGOINSERT INTO MainSELECT 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 aSET C2='X VALUE', UserID='MyDoggieJessie'FROM Main aWHERE ID IN (2, 3)DELETE FROM Main WHERE ID = 4-- Add an update to the audit trail on any DELETE-- to capture the deleting userUPDATE Audit SET UserID = 'MyCatHobbsy'WHERE RecordID = 4 AND NewValue IS NULLSELECT * FROM MainSELECT * FROM AuditDROP TRIGGER [dbo].[tr_Audit]DROP TABLE MainDROP TABLE Audit[/code]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.[b]Edit:[/b] Silly me! Forgot the WHERE clause in the trigger.Tue, 22 Jan 2013 22:13:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspx[quote][b]MyDoggieJessie (1/22/2013)[/b][hr]Beat to the punch on this one :-DDwain has made some very valid points! @Dwain, are you referring to CDC (Change Data Capture)?[/quote]Yeah! CDC is it.Tue, 22 Jan 2013 21:50:20 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxBeat to the punch on this one :-DDwain has made some very valid points! @Dwain, are you referring to CDC (Change Data Capture)?Tue, 22 Jan 2013 21:47:49 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxYou 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 table2) 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, PriorValueFirst trigger (for the original inserts)[code="sql"]CREATE TRIGGER [dbo].[tr_NewRecord] ON [dbo].[TriggerTest] AFTER INSERTASBEGINSET 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 CATCHEND[/code]Test.Second Trigger (for the updates)[code="sql"]CREATE TRIGGER [dbo].[tr_RecordUpdated] ON [dbo].[TriggerTest] AFTER UPDATEASBEGINSET 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 CATCHEND[/code]Tue, 22 Jan 2013 21:46:38 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxHi 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 valueOld 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.Tue, 22 Jan 2013 21:19:02 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410313-392-1.aspxIn 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?Tue, 22 Jan 2013 20:02:03 GMTwendy elizabeth