complex and large triggers

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"Gerard" <g.doeswijk@.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.
Gerard,
May I ask what you mean by "If"? Can you rephrase the
statement/question?

> Thes triggers pre-populate
> tables with data based on gathered business rules and make
> use of nested stored procedures and cursors.
Ok, I understand what the triggers are doing (or, at least, I think
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.|||On 28 Dec 2005 13:22:26 -0800, "Gerard" <g.doeswijk@.gmail.com> wrote:
>Anyone have any experience already with managed code when it comes to
>embedding business logic into the database that they want to share?
Over the last ten years or so, until recently, the standard
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|||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.
"Gerard" <g.doeswijk@.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
>|||> May I ask what you mean by "If"?
When a business scenario related to data gathering for statistical
analysis 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/micr...1783d773?hl=en&|||> Why? Because TSQL was never the best language for capturing complex (proc
edural) 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
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.|||> 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|||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
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1135861795.693314.17140@.z14g2000cwz.googlegroups.com...
> 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
>|||Gerard wrote:
> 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
I thought Brian explained that: Calling procs from a trigger implies
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
> 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.
This sounds like EAI (Enterprise Application Integration). While
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
--|||> What is your SP is failed and for some reasons this trasaction is still op
ened
Could proper error handling around the calling of a store proc not take
care of that?