Thanks again for your reply. Unfortunately, I need to pass the userid prior to executing each query as otherwise the auditing function would not know the userid. We have triggers on tables but triggers need the read the userid apriori.
ColdFusion does not allow us to build our connection string dynamically or to dynamically inject connection string variables (such as the userid), as is the case with VB.NET and C#. This is why I am looking for an alternative. >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 > >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? > >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:309442 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
