|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
complex and large triggersIf a business scenario related to data gathering for statistical
analysis merits large complex triggers. Thes triggers pre-populate tables with data based on gathered business rules and make use of nested stored procedures and cursors. What is then good practice? Are cursors to be kept small and lean? Should I switch over to Sql Server 2005? (that's not really a question that) Anyone have any experience already with managed code when it comes to embedding business logic into the database that they want to share? regards, Gerard On 28 Dec 2005 13:22:26 -0800, "Gerard" <g.doesw***@gmail.com> wrote: Over the last ten years or so, until recently, the standard>Anyone have any experience already with managed code when it comes to >embedding business logic into the database that they want to share? architecture has been to put business logic in a middle tier. Now, it turns out that is almost impossible, as "business logic" determines the data model of a database, much less even a standard set of well-designed stored procedures. But in general, it was good advice, at least on SQLServer. Why? Because TSQL was never the best language for capturing complex (procedural) business logic. Now, Oracle has always had PL/SQL, a much more procedural language, and has had Java applets for several years. Now that SQLServer 2005 officially has .Net languages also available "inside" the database, maybe that would be the tool to use to put more business logic into a database. -- So, "embed business logic" in the database by constructing a good normalized data model, and be prepared to service a middle-tier with well-constructed stored procedures to encapsulate fancy SQL. If you also construct the "middle-tier" with C# inside of SQLServer 2005, well and good, I guess. Have to get a chance to try it myself ... Josh > Why? Because TSQL was never the best language for capturing complex (procedural) business logic. I agree, there are better languages to do so such as C#. What I'mreferring to and curious about is those scenarios where there is no way to get at the middle tier of an existing application (vendor locked) and alternatives need to be sought using the database. For instance, you have no control over the business logic tier other then modifying the vendors stored procedures (highly undesirable when it comes to upgrades/service packs etc.) and need to build custom business logic based upon database events that are initiated by the vendors applications. On 29 Dec 2005 05:35:54 -0800, "Gerard" <g.doesw***@gmail.com> wrote: Ouch.>> Why? Because TSQL was never the best language for capturing complex (procedural) business logic. > >I agree, there are better languages to do so such as C#. What I'm >referring to and curious about is those scenarios where there is no way >to get at the middle tier of an existing application (vendor locked) >and alternatives need to be sought using the database. >For instance, you have no control over the business logic tier other Well, sometimes you have no choice.>then modifying the vendors stored procedures (highly undesirable when >it comes to upgrades/service packs etc.) and need to build custom >business logic based upon database events that are initiated by the >vendors applications. But you're obviously a long way from anyone's idea of best practices. Josh "Gerard" <g.doesw***@gmail.com> wrote in message Gerard,news:1135804946.733346.3910@z14g2000cwz.googlegroups.com... > If a business scenario related to data gathering for statistical > analysis merits large complex triggers. May I ask what you mean by "If"? Can you rephrase the statement/question? > Thes triggers pre-populate Ok, I understand what the triggers are doing (or, at least, I think> tables with data based on gathered business rules and make > use of nested stored procedures and cursors. I do). How does this correlate with the previous sentence? > What is then good practice? I will need more information on what you are trying to do.The link http://www.aspfaq.com/etiquette.asp?id=5006, is excellent when it comes to detailing how to provide the information that will best enable others to answer your questions. > Are cursors to be kept small and lean? Cursors should be used only when absolutely nothing else can be done(usually for manipulating freshly imported data that has not yet been "normalized"). Oh, and never put a cursor in a trigger. Sincerely, Chris O. > May I ask what you mean by "If"? When a business scenario related to data gathering for statisticalanalysis merits large complex triggers > Oh, and never put a cursor in a trigger. I think that was covered in this post already:http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/1a9fe7d71783d773?hl=en& Cursors should be avoided whenever possible and practical. Set-based
operations usually perform much, MUCH faster than cursors for a number of reasons: (1) index updates can be combined which reduces writes; (2) triggers fire once per operation reducing processing overhead; (3) transaction logging overhead is reduced as well. There are few exceptions--mostly involving self-joins. Even then, you should try using set-based operations first, and only after careful examination and analysis replace contraindicated set-based code with a cursor. Also, if you must use a cursor, avoid reads and writes within the fetch loop--that is, include everything you need in the SELECT statement for the cursor, or process several cursors with result sets in the same order in the same fetch loop, and cache writes in temporary objects such as table variables so that they can be flushed outside of the fetch loop. This minimizes the performance impact of using a cursor. In general, I prefer not to call stored procedures from within triggers. Cursors are almost always bad, so whether you use them in a trigger or not doesn't change that fact. What should be noted is that if you manipulate a table within a cursor fetch loop, then the triggers on that table will fire once per iteration. This can increase the probability of blocking and deadlocks. Show quote "Gerard" <g.doesw***@gmail.com> wrote in message news:1135804946.733346.3910@z14g2000cwz.googlegroups.com... > If a business scenario related to data gathering for statistical > analysis merits large complex triggers. Thes triggers pre-populate > tables with data based on gathered business rules and make > use of nested stored procedures and cursors. What is then good > practice? > > Are cursors to be kept small and lean? > > Should I switch over to Sql Server 2005? (that's not really a question > that) > > Anyone have any experience already with managed code when it comes to > embedding business logic into the database that they want to share? > > regards, > > Gerard > > In general, I prefer not to call stored procedures from within triggers. Can you tell me why? Is that just because you like to have all yourlogic in the one script or are there other reasons? regards, Gerard Gerard
Well , if a trigger gets fired actually SQL Server "opens" another transaction to perform any DML in your case to call a stored procedure. What is your SP is failed and for some reasons this trasaction is still opened , so end-users will no be able to perform somethin till this one will be completed . I prefer to keep a code within a trigger as small as possible , and using triggers only for auditing. But it's my opinion Show quote "Gerard" <g.doesw***@gmail.com> wrote in message news:1135861795.693314.17140@z14g2000cwz.googlegroups.com... >> In general, I prefer not to call stored procedures from within triggers. > > Can you tell me why? Is that just because you like to have all your > logic in the one script or are there other reasons? > > regards, > > Gerard > > What is your SP is failed and for some reasons this trasaction is still opened Could proper error handling around the calling of a store proc not takecare of that? Gerard wrote:
> > In general, I prefer not to call stored procedures from within triggers. I thought Brian explained that: Calling procs from a trigger implies> > Can you tell me why? Is that just because you like to have all your > logic in the one script or are there other reasons? > > regards, > > Gerard that you'll need to use a cursor (not always but typically that will be the case). Cursor code is bad news for lots of reasons that have been covered here many times. > For instance, you have no control over the business logic tier other This sounds like EAI (Enterprise Application Integration). While> then modifying the vendors stored procedures (highly undesirable when > it comes to upgrades/service packs etc.) and need to build custom > business logic based upon database events that are initiated by the > vendors applications. triggers may form part of those solutions, more commonly other tools are used such as messaging or integration software. Normally you can add your own code to that integration process although some tools do claim code-free solutions. Have you looked at third-party integration tools? Do that before you decide to invent your own. -- David Portas SQL Server MVP -- > Have you looked at third-party integration tools? Do that before you decide to invent your own. No, but that's a so-called management constraint.Gerard wrote:
> > Have you looked at third-party integration tools? Do that before you decide to invent your own. Well, you asked about good practice not about how to satisfy your> > No, but that's a so-called management constraint. boss... Actually, I'm surprised you give that answer. Many shops require off-the-shelf solutions to be evaluated first as a matter of due diligence at the start of a development project. Of course you can roll-your-own instead. I'd suggest that triggers may not be the way to go however. My point is that the application integration doesn't necessarily have to sit in a database transaction and there are potential advantages to other models. Take a look at: http://www.enterpriseintegrationpatterns.com/ -- David Portas SQL Server MVP -- > My point is that the application integration doesn't necessarily have to sit in a database transaction The reason behind that is that I'm working with un-finished goods fromthe vendor, e.g. half of the new functionality is delivered but we need to go live with a hundred percent, so build our own is the only option right now. This situation is far from ideal I agree but then the real constraint here is time, and since the vendor is microsoft that other half can take a while.. ;-) A stored procedure executing within the context of a trigger executes within
the context of a transaction as well. In addition, Error handling and rollbacks operate differently within triggers. Most stored procedures I write wait until the last instant to start a transaction. I prefer to use optimistic concurrency with rowversioning because it's been my experience that it is far less costly in performance, concurrency and scalability to detect collisions than to try to avoid them. All of this goes right out the window if the transaction has already been started before the procedure is called. Also, a rollback within a stored procedure doesn't terminate the batch (unless XACT_ABORT is ON), whereas a rollback within a trigger does. Consequently, if the stored procedure is executed within a trigger then it must be able to deal with both modes of operation--both for error handling and for rollbacks. The bottom line is that stored procedures cost less to produce if they're not going to be called from triggers. Show quote "Gerard" <g.doesw***@gmail.com> wrote in message news:1135861795.693314.17140@z14g2000cwz.googlegroups.com... >> In general, I prefer not to call stored procedures from within triggers. > > Can you tell me why? Is that just because you like to have all your > logic in the one script or are there other reasons? > > regards, > > Gerard > Clarification: When XACT_ABORT is ON an error causes any outstanding
transaction to roll back and terminates the batch. Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:ugaaknMDGHA.3064@TK2MSFTNGP10.phx.gbl... >A stored procedure executing within the context of a trigger executes >within the context of a transaction as well. In addition, Error handling >and rollbacks operate differently within triggers. Most stored procedures >I write wait until the last instant to start a transaction. I prefer to >use optimistic concurrency with rowversioning because it's been my >experience that it is far less costly in performance, concurrency and >scalability to detect collisions than to try to avoid them. All of this >goes right out the window if the transaction has already been started >before the procedure is called. Also, a rollback within a stored procedure >doesn't terminate the batch (unless XACT_ABORT is ON), whereas a rollback >within a trigger does. Consequently, if the stored procedure is executed >within a trigger then it must be able to deal with both modes of >operation--both for error handling and for rollbacks. > > The bottom line is that stored procedures cost less to produce if they're > not going to be called from triggers. > > "Gerard" <g.doesw***@gmail.com> wrote in message > news:1135861795.693314.17140@z14g2000cwz.googlegroups.com... >>> In general, I prefer not to call stored procedures from within triggers. >> >> Can you tell me why? Is that just because you like to have all your >> logic in the one script or are there other reasons? >> >> regards, >> >> Gerard >> > > Brian,
I tried to locate this setting on SQL Server 2000 but could not find it, it is in BOL as one of the user options though. On SQL Server 2005 it is visible, any ideas why I cannot see this? I'm using Enterprose Manager, right click the server, properties, connections tab to get to the setting as described in BOL. regards, Gerard Gerard wrote:
> Brian, XACT_ABORT is a connection setting, not a database or server setting.> > I tried to locate this setting on SQL Server 2000 but could not find > it, it is in BOL as one of the user options though. On SQL Server 2005 > it is visible, any ideas why I cannot see this? > > I'm using Enterprose Manager, right click the server, properties, > connections tab to get to the setting as described in BOL. > > regards, > > Gerard There are plenty of things that aren't available in Enterprise Manager. That won't bother you if you avoid EM - in fact you'll be better off because you'll have a better understanding of what goes on behind the scenes. I rarely use EM to make any kind of database or server modifications. The syntax is: SET XACT_ABORT ON SET XACT_ABORT OFF -- David Portas SQL Server MVP -- David,
below is from BOL on user options Option: ---- How to configure user options (Enterprise Manager) To configure user options Expand a server group. Right-click a server, and then click Properties. Click the Connections tab. In the Default connection options box, select one or more attributes to configure the default query-processing options for all connected users. By default, no user options are configured. --- This looks to me as something which can be set on a per server basis. Correct me if I'm wrong but because this setting is available in SQL Server 2005 I would expect to see it in 2000 also. regards, Gerard Gerard (g.doesw***@gmail.com) writes:
Show quote > below is from BOL on user options Option: Yes, it is correct that you can make SET XACT_ABORT ON the default setting> > ---- > How to configure user options (Enterprise Manager) > To configure user options > > Expand a server group. > Right-click a server, and then click Properties. > Click the Connections tab. > In the Default connection options box, select one or more attributes to > configure the default query-processing options for all connected users. > > By default, no user options are configured. > > --- > > This looks to me as something which can be set on a per server basis. > Correct me if I'm wrong but because this setting is available in SQL > Server 2005 I would expect to see it in 2000 also. on server level. (Both in SQL 2000 and SQL 2005.) However, it's still a session-level setting in that in can be overridden by a session. "user options" only sets a default. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland
> "user options" only sets a default. Ok, got thatAny ideas on why I still can't see this setting in EM when I go through the steps I described earlier? regards, Gerard Gerard wrote:
> Erland Because it isn't there AFAIK. EM maintains the default connection> > > "user options" only sets a default. > > Ok, got that > > Any ideas on why I still can't see this setting in EM when I go through > the steps I described earlier? > > regards, > > Gerard settings at the client and sets them for each new connection but as XACT_ABORT is a runtime setting and EM doesn't directly support the ability to run multi-statement scripts interactively it would be pretty redundant to provide this setting through the EM interface. I expect that's why it isn't there. Management Studio on the other hand is a script development tool and does provide such an option. In your code, do remember to set this explicitly when you open a connection. The default in other client connections may not be what you expect in Studio. -- David Portas SQL Server MVP -- Gerard (g.doesw***@gmail.com) writes:
> Erland I believe "user options" is only visible, if you have enabled the > >> "user options" only sets a default. > > Ok, got that > > Any ideas on why I still can't see this setting in EM when I go through > the steps I described earlier? configuration option "Show advanced options". (Which is disabled by default, and which I enable about the first I do when I start to work with a server.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx show advanced options is enabled, but the XACT_ABORT is not visible in
EM in my case. Gerard (g.doesw***@gmail.com) writes:
> show advanced options is enabled, but the XACT_ABORT is not visible in You will have to look hard. :-) It just a single bit in the option "user > EM in my case. options". If this option has a value >= 16384, XACT_ABORT ON is the default for the server, else not. Again, I like to stress that SET XACT_ABORT, like anything else controlled by the SET command, is a session-level option. That option merely gives you a possibility to set a server-wide default. (And I would not recommend doing that.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx it seems I also have to look a bit harder at what is written in BOL,
missed the "use the user options". Anyway it is clear to me now, I need this info to check if the server was set to use XACT_ABORT and I know where to look now. thanks, Gerard  |
|||||||||||||||||||||||
Other interesting topics