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