How widespread is the use of triggers these days and how concerned about performance?....

Nari Kannan wrote:

> Fifteen years ago when databases were primarily used in the context of
> transaction-intensive applications, triggers were a nice feature of a
> DBMS system that did not get used because of performance concerns.

I disagree. This opinion on triggers is similar to that on referential
integrity (RI) 15 years ago - the logic exercised by the database to
maintain referential integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the same if not more
- just now the application becomes "slower" versus the rdbms. Whether
the logic of triggers or RI is performed in the application or the
database, it's going to have similar pathlength - the only question is
whether the rdbms or application can be more efficient. As more
applications are written in languages that emphasize portability over
performance (Java) or ease of learning over performance (Visualbasic),
the benefits of putting logic that centres around data further down into
the database engine become stronger.

>
> Fast forward 15 years, it seems to me that 95% of database usage is
> for
> non-transaction intensive information (I could be wrong in this).

I doubt this, but I have no hard evidence to prove you wrong. I suspect
that Gartner or some other consulting group has broken down the rdbms
market amonght OLTP, BI, OLAP, Data Mining and so forth. I think that
OLTP would be more than 5%. Certianly there has been a lot of growth in
ERP/CRM companies (SAP, Peoplesoft, Siebel, Baan, JD Edwards) over the
last fifteen years, and their premier applications tend to be
OLTP-intensive.

> Given this I see increasing usage of triggers for doing various
> necessary things that regular reporting does not do.
>
> Am I just dreaming or that triggers are being increasingly used?

Yes, they are used increasingly. A search on triggers in www.deja.com
should show this.

>
> Are performance concerns not important in the above context as long as
> you
> are mindful of stuff like cascading triggers and keeping the trigger
> code short?
>
> Thanks in advance for your insights, experiences and opinions.
>
> regards
> Nari
>
> Nari Kannan

Nari Kannan wrote:

> Fifteen years ago when databases were primarily used in the context of
> transaction-intensive applications, triggers were a nice feature of a
> DBMS system that did not get used because of performance concerns.

I disagree. This opinion on triggers is similar to that on referential
integrity (RI) 15 years ago - the logic exercised by the database to
maintain referential integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the same if not more
- just now the application becomes "slower" versus the rdbms. Whether
the logic of triggers or RI is performed in the application or the
database, it's going to have similar pathlength - the only question is
whether the rdbms or application can be more efficient. As more
applications are written in languages that emphasize portability over
performance (Java) or ease of learning over performance (Visualbasic),
the benefits of putting logic that centres around data further down into
the database engine become stronger.

>
> Fast forward 15 years, it seems to me that 95% of database usage is
> for
> non-transaction intensive information (I could be wrong in this).

I doubt this, but I have no hard evidence to prove you wrong. I suspect
that Gartner or some other consulting group has broken down the rdbms
market amonght OLTP, BI, OLAP, Data Mining and so forth. I think that
OLTP would be more than 5%. Certianly there has been a lot of growth in
ERP/CRM companies (SAP, Peoplesoft, Siebel, Baan, JD Edwards) over the
last fifteen years, and their premier applications tend to be
OLTP-intensive.

> Given this I see increasing usage of triggers for doing various
> necessary things that regular reporting does not do.
>
> Am I just dreaming or that triggers are being increasingly used?

Yes, they are used increasingly. A search on triggers in www.deja.com
should show this.

>
> Are performance concerns not important in the above context as long as
> you
> are mindful of stuff like cascading triggers and keeping the trigger
> code short?
>
> Thanks in advance for your insights, experiences and opinions.
>
> regards
> Nari
>
> Nari Kannan

Hi,
I grew up in the school of hard knocks when it comes to programming
and DBs
(no university degree) so my opinion might vary with others ;-) I consider
myself a generalist rather than a specialist when it comes to systems.
Whilst I am a programmer by trade, I consider part of my to ensure that the
data is stored correctly too. I.e. That the design is good from front to
back.

I've always been an avocate of triggers and stored procedures for several
reasons.

1. The simplify the applications running them.
2. They ensure that business logic is always applied no matter how badly the
application is written.
3. Performance wise they are always faster because they are with the
DB/server.

I've encountered a number of people who are the direct opposite of this.
I.e. they refuse to allow any triggers, procs or referential integredy. The
reasoning they almost always offer me is that by having non of this, the
design is more portable to other RDBMS's, easier to maintain etc.

Whilst they are valid reasons, I have (so far) always found that the
database in question is a complete mess. Missing data, inconsistant/poor
design, orphan data and tables, etc. Not to mention the applications trying
to run them.

In reality I think that the real reasons for this attitude are more to do
with: 1. Laziness. (I.e. they can't be bothered writing the necessary sqls
to correctly build and maintain their DBs). 2. Lack of knowledge of
databases, and 3. An un-willingness to learn.

Surprising to me has been a fairly regular attitude amongst programmers that
a database is just a place to store information. Like it was a file or
something. They have no interest is ensuring the data their precious
application is sending is stored correctly or reliably, and don't care
about maintenace of it.

I have yet to find a database/application that would not be better off with
a proper set of stored proces and and triggers. I'm sure they are out
there. Just haven't seen one in my experience.

Sorry for the sermon ;-)

Derek.

Hi,
I grew up in the school of hard knocks when it comes to programming
and DBs
(no university degree) so my opinion might vary with others ;-) I consider
myself a generalist rather than a specialist when it comes to systems.
Whilst I am a programmer by trade, I consider part of my to ensure that the
data is stored correctly too. I.e. That the design is good from front to
back.

I've always been an avocate of triggers and stored procedures for several
reasons.

1. The simplify the applications running them.
2. They ensure that business logic is always applied no matter how badly the
application is written.
3. Performance wise they are always faster because they are with the
DB/server.

I've encountered a number of people who are the direct opposite of this.
I.e. they refuse to allow any triggers, procs or referential integredy. The
reasoning they almost always offer me is that by having non of this, the
design is more portable to other RDBMS's, easier to maintain etc.

Whilst they are valid reasons, I have (so far) always found that the
database in question is a complete mess. Missing data, inconsistant/poor
design, orphan data and tables, etc. Not to mention the applications trying
to run them.

In reality I think that the real reasons for this attitude are more to do
with: 1. Laziness. (I.e. they can't be bothered writing the necessary sqls
to correctly build and maintain their DBs). 2. Lack of knowledge of
databases, and 3. An un-willingness to learn.

Surprising to me has been a fairly regular attitude amongst programmers that
a database is just a place to store information. Like it was a file or
something. They have no interest is ensuring the data their precious
application is sending is stored correctly or reliably, and don't care
about maintenace of it.

I have yet to find a database/application that would not be better off with
a proper set of stored proces and and triggers. I'm sure they are out
there. Just haven't seen one in my experience.

Sorry for the sermon ;-)

Derek.

I have been teaching Database Design and administration (for a major
vendor) and a working, hands-on DBA for 20+ years. One thing I have
learned and tried to pass on to my students is that ultimately "the
buck stops here." It does not matter that a programmer's faulty logic
or the careless use of an interactive SQL tool corrupted the database,
it's the DBA's fault for not preventing it from happening.

The average DBA can not enforce programming standards or review code -
time and policy usually will not allow it. And today's databases are
updated from a multitude of sources outside the immediate control of
the server and the DBA. I have seen production databases destroyed by
end users and an Excel spreadsheet because by policy update rights
were not removed. And what DBA hasn't taken the rap for poor
application performance?

Where mainframe based OLTP platforms managed problems such as
deadlocks and rollbacks, the client-server DBMS systems of today leave
much of those issues to the programmer to design, program, and react
to. And let me ask - have you ever met a programmer who really
understood concurrency?

So, to me, triggers, check constraints, and referential integrity
become the last bastion of control. I do not wish to do a
programmer's job for him and I consider many of the UDF's, Stored
Procedures, and even triggers issues that could and should be handled
at the program level. But to paraphrase the marketing slogan for an
overnight delivery company: When it absolutely, positively got to be
done ....

I have been teaching Database Design and administration (for a major
vendor) and a working, hands-on DBA for 20+ years. One thing I have
learned and tried to pass on to my students is that ultimately "the
buck stops here." It does not matter that a programmer's faulty logic
or the careless use of an interactive SQL tool corrupted the database,
it's the DBA's fault for not preventing it from happening.

The average DBA can not enforce programming standards or review code -
time and policy usually will not allow it. And today's databases are
updated from a multitude of sources outside the immediate control of
the server and the DBA. I have seen production databases destroyed by
end users and an Excel spreadsheet because by policy update rights
were not removed. And what DBA hasn't taken the rap for poor
application performance?

Where mainframe based OLTP platforms managed problems such as
deadlocks and rollbacks, the client-server DBMS systems of today leave
much of those issues to the programmer to design, program, and react
to. And let me ask - have you ever met a programmer who really
understood concurrency?

So, to me, triggers, check constraints, and referential integrity
become the last bastion of control. I do not wish to do a
programmer's job for him and I consider many of the UDF's, Stored
Procedures, and even triggers issues that could and should be handled
at the program level. But to paraphrase the marketing slogan for an
overnight delivery company: When it absolutely, positively got to be
done ....

"Fred Prose" wrote in message
news:195a4770.0308040641.5e3682c5@posting.google.com...
> I have been teaching Database Design and administration (for a major
> vendor) and a working, hands-on DBA for 20+ years. One thing I have
> learned and tried to pass on to my students is that ultimately "the
> buck stops here." It does not matter that a programmer's faulty logic
> or the careless use of an interactive SQL tool corrupted the database,
> it's the DBA's fault for not preventing it from happening.


> So, to me, triggers, check constraints, and referential integrity
> become the last bastion of control. I do not wish to do a
> programmer's job for him and I consider many of the UDF's, Stored
> Procedures, and even triggers issues that could and should be handled
> at the program level. But to paraphrase the marketing slogan for an
> overnight delivery company: When it absolutely, positively got to be
> done ....

lets invalidate the support contract...? Seriously much of what you say
makes sense only in the context of an organization that

a) has well defined business rules and
b) has in-house development.


What are you going to do with SAP,PSFT,Oracle Financials,JDE etc? slap some
extra constraints and RI on them?

Cos IMO that is what most DBAs are dealing with.

"Fred Prose" wrote in message
news:195a4770.0308040641.5e3682c5@posting.google.com...
> I have been teaching Database Design and administration (for a major
> vendor) and a working, hands-on DBA for 20+ years. One thing I have
> learned and tried to pass on to my students is that ultimately "the
> buck stops here." It does not matter that a programmer's faulty logic
> or the careless use of an interactive SQL tool corrupted the database,
> it's the DBA's fault for not preventing it from happening.


> So, to me, triggers, check constraints, and referential integrity
> become the last bastion of control. I do not wish to do a
> programmer's job for him and I consider many of the UDF's, Stored
> Procedures, and even triggers issues that could and should be handled
> at the program level. But to paraphrase the marketing slogan for an
> overnight delivery company: When it absolutely, positively got to be
> done ....

lets invalidate the support contract...? Seriously much of what you say
makes sense only in the context of an organization that

a) has well defined business rules and
b) has in-house development.


What are you going to do with SAP,PSFT,Oracle Financials,JDE etc? slap some
extra constraints and RI on them?

Cos IMO that is what most DBAs are dealing with.

>> Am I just dreaming or that triggers are being increasingly used? <<

I would guess less used. DRI actions, DEFAULTs and CHECK()
constraints do what TRIGGERs used to do in procedural code. Being
predicates, these features can also send information ot the
optimizers, which triggers and procedures cannot.