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