Dale: does it have to be done at the DB level, or can it be via the DAL? This is one of the reasons I like the concept of AOP - squeeze in there to add auditing/logging without mucking up too much code.
On Thu, Aug 19, 2010 at 8:30 AM, Scott Thornton <scott.thorn...@hnehealth.nsw.gov.au> wrote: > easy no, possible yes. > > > > check out 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://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. > -- 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.
