You could make them fire based upon a null value or value not being set at all. For example if UserID is in fact passed in then do not fire trigger but if UserID is not passed in then fire the trigger. I think that would then work fine for the CF pages interacting with things and then when manually interacting so long as the person doing the queries remembers the triggers are in place then they could bypass them that way. Although if someone knew they were in place they could also just disable the trigger, run whatever they needed to do and then re-enable the trigger.
On Tue, Jul 22, 2008 at 7:20 AM, Experienced CF Developer < [EMAIL PROTECTED]> wrote: > 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:309441 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
