Thanks for the kind words, Tony! :) Kevin, it certainly is true that you can call stored procedures from within triggers, but your stored procedures will not have direct access to the Inserted and Deleted pseudotables (if you are using SQL Server) that are created and accessible only within the trigger itself.
The logic for calling stored procedures from within Oracle's row-level triggers is much more straightforward because the :New and :Old pseudorows are single instances of directly accessible data, rather than the _sets_ of new and old (inserted and deleted) data accessible within SQL Server's statement-level triggers. Accessing, manipulating, and joining to the new and old images of data are the most powerful features of triggers, aside from the fact that they are the only method for reliably binding business logic to data. With SQL Server's statement-level triggers, pseudotables also provide the only scalable way to perform user-defined relational updates or relational deletes between tables. Calling stored procedures from triggers are fine when you are performing either tasks that don't require parameters, or when you can reliably restrict your pseudotable's data set to a single instance (row) that you can directly access like you would an Oracle pseudorow, and then pass those values as parameters to your called stored procedures. Beware restricting your triggers' pseudotables to single rows, though, as that also restricts the triggering table to single-row transactions, and that is a slippery slope that quickly leads to destroying your relational database's ability to enforce its DRI (Declarative Referential Integrity) constraints. It gets tempting to guarantee a single row in either the Inserted or Deleted pseudotables by rolling back everything if @@ROWCOUNT > 1 in the trigger, but most of the time that blocks other processes from successfully completing elsewhere in your database, so beware. Happy triggering! :) Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion 5 Developer Productivity Enhancement, Inc. http://www.ColdFusionTraining.com E-mail: [EMAIL PROTECTED] Phone: 770-446-8866 ----- Original Message ----- From: "Kevin Gilchrist" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, April 03, 2002 6:24 PM Subject: RE: Where is the best place to put business rules? > This is what we do too with a slight difference in that our triggers > call stored procedures. > Sometimes triggers may need to perform similar business functions > (look-ups etc.) so this way we can maintain a common set of functions. > > And because we call stored procedures we never have to think of the > security concerns (and overhead) of checking URL vars for SQL > statements. > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 03, 2002 4:19 PM > To: CF-Talk > Subject: RE: Where is the best place to put business rules? > > > in the great words of Adam Churvis. > > What would happen if someone decided to update your database using Excel > ODBC connection? The stored procedure wouldn't run. But if you had your > business logic in the trigger, it would fired upon an insert, update, or > delete. > > > That man knows best. Since then I put all my business logic inside of > triggers. > > > Anthony Petruzzi > Webmaster > 954-321-4703 > http://www.sheriff.org > > > -----Original Message----- > From: Kola Oyedeji [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 03, 2002 4:17 PM > To: CF-Talk > Subject: RE: Where is the best place to put business rules? > > > I've often wondered about this is there really a need for a third tier, > what > would you put in the third tier that you *couldn't* put in a stored proc > apart from file operations? > > Thanks > > Kola > > > >-----Original Message----- > > >From: Dave Watts [mailto:[EMAIL PROTECTED]] > > >Sent: 03 April 2002 15:02 > > >To: CF-Talk > > >Subject: RE: Where is the best place to put business rules? > > > > > > > > >> 1) Where is the best place to put the business rules? CF > > >> Templates (using CFTransaction), or Stored Procedures (using > > >> SQL Transactions). We realize that some business rules need > > >> to reside on the templates, such as client side data validation. > > >> But processing pages are a different story. > > > > > >In general, I think that stored procedures are better for any > > >generic data > > >manipulation tasks. Using stored procedures provides many > > >advantages, such > > >as more efficient processing and the ability to more easily replace > or > > >augment the presentation layer code. > > > > > >You did mention n-tier apps, and it's worth pointing out that in a > > >three-tier app, the things you generally think of as "business > > >rules" would > > >be implemented in the middle tier, between the presentation > > >layer code (CF, > > >in this case) and the database. However, to the extent that your > business > > >rules are data manipulation issues, you're better off putting them in > the > > >database tier of your two-tier application. > > > > > >> 2) What is more likely to need clustering - the Web/CF Server, > > >> or the SQL Server. > > > > > >The web servers are more likely to benefit from clustering, in > general. > > > > > >Dave Watts, CTO, Fig Leaf Software > > >http://www.figleaf.com/ > > >voice: (202) 797-5496 > > >fax: (202) 797-5444 > > > > > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
