[cfaussie] Re: CFUG Melbourne August 2010: New features in Adobe ColdFusion Server 9 by Steve Onnis
I will be there, see you folks tomorrow! -- 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.
[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://dale.fraser.id.au http://cfmldocs.com/ http://cfmldocs.com http://learncf.com http://learncf.com http://flexcf.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.
RE: [cfaussie] Database Changes
easy no, possible yes. check out www.sqlservercentral.comhttp://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.comhttp://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.
Re: [cfaussie] Database Changes
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.
Re: [cfaussie] Database Changes
Here here! Mark On Thu, Aug 19, 2010 at 8:37 AM, Barry Beattie barry.beat...@gmail.comwrote: 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. -- E: mark.man...@gmail.com T: http://www.twitter.com/neurotic W: www.compoundtheory.com cf.Objective(ANZ) - Nov 18, 19 - Melbourne Australia http://www.cfobjective.com.au Hands-on ColdFusion ORM Training www.ColdFusionOrmTraining.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.
Re: [cfaussie] CFUG Melbourne August 2010: New features in Adobe ColdFusion Server 9 by Steve Onnis
Count me in Cheers, Steve Armstrong On 17 August 2010 12:21, Peter Robertson pe...@p-robertson.com wrote: This presentation will cover some of the new functionality introduced in the new version of the Adobe ColdFusion Server 9 - ColdFusion 9 Goodies include: - CFSCRIPT enhancements - ColdFusion Component enhancements - UI Components - New Tags - Lucene Solr (Verity Replacement) Also we will be handing out bags of goodies with t-shirts, stubby holders, stickers, note pads and other stuff and of course pizzas. I think all of the Melbourne regulars will know Steve as our UG manager and provider of fine pizzas. Anyone who frequents CFAussie will also be aware of his regular contributions to the list. As a developer and CF hosting provider, Steve has spent years exploring ColdFusion Server and CFML, so don't miss this opportunity for a deep dive into all that is new and good in CF9. Date: Thursday 19 August 2010 Time: 6:30 PM Location: CogState Level 2 255 Bourke Street Melbourne, VIC, 3000 A note on the door will provide a number to ring for access. RSVP: Please reply to this post if you are planning to attend so we know how many pizzas to order. As always, many thanks to Dale Fraser and CogState for their hospitality. We look forward to seeing you all there. Peter Robertson Co-Manager Melbourne CFUG Steve Onnis Manager Melbourne CFUG -- 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.comcfaussie%2bunsubscr...@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.
[cfaussie] Re: Database Changes
AOP would be nice... but if the code your are running your PointCuts on isn't quite set-up for it, its not the right solution. Do you have versioning on your records? This would make it a lot easier but from my experience, auditing is a feature requirement that is included quite late... On Aug 19, 10:19 am, Mark Mandel mark.man...@gmail.com wrote: Here here! Mark On Thu, Aug 19, 2010 at 8:37 AM, Barry Beattie barry.beat...@gmail.comwrote: 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. -- E: mark.man...@gmail.com T:http://www.twitter.com/neurotic W:www.compoundtheory.com cf.Objective(ANZ) - Nov 18, 19 - Melbourne Australiahttp://www.cfobjective.com.au Hands-on ColdFusion ORM Trainingwww.ColdFusionOrmTraining.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.
