Joseph, I think Gert's suggestion earlier could work for you. You could design a trigger to fire the stored procedure prior to any Inserts or Updates. Check out this page on oracle site for some direction:
http://tinyurl.com/5sjhlg I haven't done anything with triggers myself. But if you want to avoid re-coding your CFML, I think this is your route to go. Of course, this will only work for you if the only thing changing your tables is your CF app. Any manual changes to the database, or any other application accessing the same database would fire the triggers as well, unless you can figure out a way to make them fire only when your app accesses the database. Hope this helps, Dave Phillips -----Original Message----- From: Joseph Bugeja [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2008 6:51 AM To: CF-Talk Subject: Re: DataSource Events like Before Commit? Thanks for your reply. Let me explain our scenario. We have a number of cfquery tags that insert, update and delete data from our Oracle database. Basically, before each query is run I need to execute a stored procedure. This stored procedure sets the userid (the user currently logged in) in the database for auditing purposes. We have hundreds of queries and it is not good from a design perspective to rewrite each query to call the same central piece of code before each query gets executed. So my original query should be: Original Query: <cfquery> DML Operation </cfquery> New requirement should be: <cftransaction> <cfstoredproc> Set the user id </cfstoredproc> <cfquery> DML Operation </cfquery> </cftransaction> As shown above, I need to call the cfstoredproc before each query is executed. Personally, I would prefer if I keep my original design and before the original query is run the ColdFusion engine automatically injects the new call in a transaction to set the clientid. Do you have any suggestions? >Joseph, > >I don't think what you are trying to do is possible. Can you give us some >more details of what you are trying to accomplish to see if we can help you >come up with an alternative solution? > >Sincerely, > >Dave Phillips > >Dear ColdFusion Users, > >Is there a way that I can attach to a datasource through an event like >before_commit or before_update? > >So, if I have a cfquery tag, and I would ColdFusion to automatically, before >ColdFusion executes the code found in the cfquery I would like it to >automatically execute another query for example to set the user id to the >database? In this way I do not need to make any modification to the existing >code except that of creating new code to do something before executing my >main query? > >Thanks in advance for your assistance. > >Regards, >Joseph. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309439 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
