easy no, possible yes.
check out www.sqlservercentral.com<http://www.sqlservercentral.com> for some
auditing techniques.
I audit quite a few of the fields in certain tables within my application.
This is done via triggers, inserting changes into an audit table: eg:
ALTER TRIGGER [tablename_UPDATE]
ON [dbo].[tablename]
FOR UPDATE
AS
/*
Author: Scott Thornton
Date: 05-07-2005
Desc: For each field modified on the x table, insert a record into the Audit
log table
and show the value of the filed before and after the update.
*/
set nocount on
SET DATEFORMAT DMY
DECLARE @SB_INVOICE_ITEM_ID AS NUMERIC
DECLARE @SB_UPDATED_BY AS VARCHAR(10)
DECLARE @SB_SERVICE_DATE_OLD AS DATETIME
DECLARE @SB_SERVICE_DATE_NEW AS DATETIME
DECLARE @SB_ITEM_QTY_OLD AS VARCHAR(5)
DECLARE @SB_ITEM_QTY_NEW AS VARCHAR(5)
-- SET VARIABLE VALUES TO THE OLD "PRE-UPDATE" VALUE
SELECT
@SB_INVOICE_ITEM_ID = SB_INVOICE_ITEM_ID ,
@SB_SERVICE_DATE_OLD = SB_SERVICE_DATE,
@SB_ITEM_QTY_OLD = ISNULL(CONVERT(VARCHAR, SB_ITEM_QTY) ,'blank')
FROM
DELETED
-- SET VARIABLE VALUES TO THE NEW "POST-UPDATE" VALUE
SELECT
@SB_SERVICE_DATE_NEW = SB_SERVICE_DATE ,
@SB_ITEM_QTY_NEW = ISNULL(CONVERT(VARCHAR, SB_ITEM_QTY) ,'blank')
FROM
INSERTED
-- THE SERVICE DATE HAS BEEN CHANGED
IF @SB_SERVICE_DATE_OLD <> @SB_SERVICE_DATE_NEW
BEGIN
INSERT INTO dbo.SB_AUDIT_LOG
(
SB_INVOICE_ITEM_ID,
SB_UPDATED_BY,
SB_UPDATE_DATE,
SB_AUDIT_EVENT
)
VALUES
(
@SB_INVOICE_ITEM_ID,
@SB_UPDATED_BY,
GETDATE(),
'Service date has been changed from ' +
isnull(CONVERT(VARCHAR,@SB_SERVICE_DATE_OLD,113 ), 'blank') + ' to ' +
isnull(CONVERT(VARCHAR, @SB_SERVICE_DATE_NEW,113), 'blank')
)
END
-- THE ITEM QUANTITY BEEN CHANGED
IF @SB_ITEM_QTY_OLD <> @SB_ITEM_QTY_NEW
BEGIN
INSERT INTO dbo.SB_AUDIT_LOG
(
SB_INVOICE_ITEM_ID,
SB_UPDATED_BY,
SB_UPDATE_DATE,
SB_AUDIT_EVENT
)
VALUES
(
@SB_INVOICE_ITEM_ID,
@SB_UPDATED_BY,
GETDATE(),
'The Item Quantity has been changed from ' + CONVERT(VARCHAR,
@SB_ITEM_QTY_OLD) + ' to ' + CONVERT(VARCHAR,@SB_ITEM_QTY_NEW)
)
END
tedious I know….
PS: don't forget that triggers only will fire on single row updates. an update
that effects multiple rows will only fire the trigger for the last row.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of
Dale Fraser
Sent: Thursday, 19 August 2010 12:42 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Database Changes
Is there an easy way to see what fields have changed in a database.
I have an audit requirement that basically needs me to determine what changed
So when I do an update, I would ideally like to know what columns and fields
changed
Something like
Name: Fred Fluntstone
Name: Fred Flintstone
Regards
Dale Fraser
http://dale.fraser.id.au
http://cfmldocs.com<http://cfmldocs.com/>
http://learncf.com
http://flexcf.com
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
