Take the 2-minute tour ×

We have been working on a complex database and client interface for the last 18 months. We are regularly adding new functionnalities to this application, and it is now used by tens of users on a daily basis in all our offices, including sites and overseas. This is just to tell you it is a REAL application with a REAL database.

Until now, we still did not have to write any stored procedures, except on a temporary basis to solve minor issues between client versions and updated database model (where the old client version will not properly update the newly created field, until everybody installs the newest version).

In the same way, we still did not need any triggers. In fact, the only SPs and triggers are the system ones, or the ones added for replication purpose.

I have the strange feeling that SPs and Triggers are mainly used to compensate for database design defaults and/or attempts to bypass database design rules, when developers consider that database optimisation has to oppose database normalisation.

The problem is that these tools are time-consuming (for both development or maintenance). Each developer shall then be very carefull using them, keeping in mind that they are the most "expensive" items to maintain in a database.

Could we consider that having none or few stored procedures / triggers in a database is a good indication of its normalisation level and/or its code maintenance cost?


Some of you have supplied fair arguments for the use of both triggers and SPs. But I keep on thinking that most of the time these tools are used in an improper or excessive way. How many triggers are set to make some fancy updates between table fields, or to recalculate totals or other aggregated data? How many SPs are used to build temporary tables for reporting issues? These are 2 among many situations where developers use these tools, and I think this usually illustrate database design/normalisation flaws.

Some others admit that use of SPs and triggers should be strictly controled. I find it necessary too.

I must confess that I am trying to find some upholding arguments, where all these SQL geeks working on our other databases look down at us, telling their friends "You know what? they do not even use SPs and Triggers! Haha!"

share|improve this question
Why the "Objective: " prefix in your title? –  Bobby Jack Oct 23 '08 at 11:31
You were right. "Objective" was a kind of "frenchy" mistake! –  Philippe Grondier Oct 23 '08 at 15:11
I don't understand why you believe that it's bad to use a stored proc to build temporary tables for reporting purposes. Sometimes it's the only way to get reports done in a reasonable amount of time (especially for really big databases). –  wcm Oct 23 '08 at 19:43
Well, I believe it's because we never had to do it! In the rare situations where the building of a temp table had to be done, we built it on the client side, then display it on screen (which is a environmentaly friendly option), and, if requested, print it out from the client's temp data. –  Philippe Grondier Oct 23 '08 at 21:40

10 Answers 10

Stored Procedures and Triggers are tools -- very specific tools for use within a database management system.

Triggers have a number of uses, from greatly simplifying the maintenance of history tables (where each row represents a past period in time for the primary table) to queueing requests for ETL to a data warehouse (depends on the specific RDBMS)

Stored procedures also have their place, whether they're invoked from the application or from SQL command line tools.

Inclusion of Stored Procedures or Triggers really has no bearing on the Normalization or "database design defaults". Their use in applications often relates directly to other requirements of the application, those of scalability, reliability, replication or other requirements that can be most effectively met by using these tools.

If you don't need 'em, don't use 'em. Do not, however, assume that the presence of triggers or stored procedures indicates poor design.

share|improve this answer
Totally agree. They are there to be used (but not abused). –  Valerion Oct 23 '08 at 11:52
I appreciated your argument related to triggers. –  Philippe Grondier Oct 23 '08 at 15:32

Could we consider that having none or few stored procedures / triggers in a database is a good indication of its normalization level and/or its code maintenance cost?

No you cannot.

Normalization and stored procedures are completely separate from each other.

My view on SP's is a layer of abstraction between the database and the people using it.

Forcing people to use the SP instead of direct CRUD operations will make it easier to change the design of the tables without breaking them.

share|improve this answer
I completely agree stored procedures are great for use wher eupdates could be coming from different programs. They also allow lots of changes to be deployed without the end users having to update versions. –  PeteT Oct 23 '08 at 11:56
I completely do not agree with you! Using SPs to "make it easier to change the design of the tables" is typically a proof that they are used to compensate design/normalisation flaws ... otherwise, why would you change the design of your tables? –  Philippe Grondier Oct 23 '08 at 15:53
When you say that SPs are a layer of abstraction between the database and the people, so is the client app. Do we really need these 2 layers of abstraction? –  Philippe Grondier Oct 23 '08 at 21:42
"why would you change the design of your tables?" how about because of new requirements from the users, that wasnt required before ? this happens more often than you might think... –  guigui42 Oct 29 '08 at 13:50
Another great use op SP is a way to grant direct access to your DB without the need of making your DB design public. This is regularly done when you want to link your DB to external systems –  Peter Oct 29 '08 at 14:10

There's nothing I hate more than coming across a huge bunch of in-line SQL in code that's got a bug in it. At least with a Stored Proc you can syntax check it, or even execute it to see where the problem may lie. Not to mention that it would be quicker than just firing queries at the DB as the execution plan is saved. I've long been of the opinion that DB code belongs in the DB, but that's just my opinion.

And triggers have their uses. They aren't always the best thing, but certainly are there for a reason.

share|improve this answer
According to my own experience, huge bunch of in-line SQL in code is usually linked to fancy data manipulation, which mostly happens where databases are poorly normalized, or where client apps do not respect the data model (example: records in database will be columns on client's screen) –  Philippe Grondier Oct 23 '08 at 15:30
I could argue the opposite - if your DB is properly normalized you may well be needing to join lots of tables to get results - this results in a big block of inline SQL. –  Valerion Oct 24 '08 at 8:36

As for stored procs, let's not forget security issues. To allow an application to run inline SQL means that your user account needs direct read, insert, update, and delete access to all of the tables. If there is ever a breach, your database is exposed.

Triggers have their place. Especially in an environment where there are a lot of database developers who may or may not know (for example) the SOX requirements that we keep a history of changes to budget info.

share|improve this answer
Can we resume SOX requirements by saying we need to know "which data was changed/by who/and when". Our client app has a "Transaction" object sending a record(s) in the "Transaction table" each time a transaction is commited. We have then an "history" module to analyse archived SQL instructions. –  Philippe Grondier Oct 23 '08 at 15:27
And if any of your business logic changes that might require that the table in question get updated by a different mechanism, you probably need to retool your transaction object. Maybe that's a good thing in your case. I don't use triggers often myself. I am, however, glad that I have the option. –  wcm Oct 23 '08 at 18:37

No. Stored procedures and triggers are used in many different ways. It depends on the circumstances, the developers, etc. For example, stored procedures are often used as a security mechanism.

The only place I've seen fit to use a trigger is when refactoring the database. So maybe you are on to something with that point. But other folks might use them in other ways.

share|improve this answer
Used sparingly, Triggers have their place; auditing springs to mind. –  Mitch Wheat Oct 23 '08 at 11:42
You're right. Auditing is another good use in some circumstances. –  ScottStonehouse Oct 23 '08 at 11:45

How do you get data back from the database? do you build SQL Strings and execute them? If so how do you validate the entries arent going to destroy the database? Stored procs help reduce the risk of this greatly just by virtue of the fact that the text is handled by the server as text and not as a command.

Stored procedures normally work much faster than executing SQL Strings against the database, it also means you dont have to write different selects for different groups of information as it can all be done by the stored proc. The ability to abstract the database from the program is also a benefit thats been raised a few times.

Lastly, I've really only used triggers for database auditing (before SQL2005 there was no in built auditing functionality) which would update tables with prev and new values of each change.

Normalisation and optimisation have nothing to do with stored procs or triggers, Normalisation and optimisation may affect how much you need to abstract your database but having to refactor your code each time you make a database change would in my opinion be much worse than using stored procs

share|improve this answer
Basically the client interface builds the INSERT, UPDATE and DELETE on the fly, comparing local recordset and table(s) structure(s). It does not allow fancy/multiple tables updates, but it's pretty efficient with a clean data model. We stopped worrying about validation procedures about 18 months ago –  Philippe Grondier Oct 23 '08 at 15:16
"fancy/multiple table updates"? If your database is normalized how do you handle updates to data that has a many to many relationship to another bit of data? –  CaffGeek Sep 14 '09 at 20:37


Using a trigger to do complex updates based on business rules is not a flaw. It is the preferred method. All business rules should be enforced at the database level if you want to maintain data integrity. There are ways other than the user interface to affect the data in the database and business rules should apply no matter the method used. That way imported data will have to follow the rules, new functionality will have to follow the rules (instead of having to remember that there are rules and find the functionality you built to enforce it), people updating data in bulk from the query tool (think raise all prices by 10%) will have to follow the rules, etc.

Recalculating totals tends to be done for speed of reporting if you do not have a separate reporting database. Do you want to slow down or lock the entire database when finance runs their quarterly reports that take hours to run because they have to calculate totals against millions of records? Or would you rather make each change to the data take a second longer? This is generally a method only used when a database gets large and before it gets large enough to cost justify having a separate reporting database. AS such, yes it is a temporary expedient, but one which can be quite necessary to keep the business running as you move from the original design to the the new one (it takes quite some time and a different set of skills to build an OLAP database).

share|improve this answer

Here is one example where SPs are ABSOLUTELY necessaries : the User Interface is only a small part of the whole application. And when the whole process occurs independantly from the users. For instance, i work on a project that involves a lot of data processing, from many different sources. So we receive those files, then we just run a Script Shell that will simple launch a SP to import all the data from the files, check them, manipulate them etc... And guess what? this same SP can be used ALSO by the user, from the user interface, without the need to rewrite the whole data processing queries again !

Of course if those processing queries were just simple SELECT, then you could argue about the necessity of SP, but when you need to UPDATE dozens of tables, calculate fields, purge data, clean data , then SPs are blessed. And its doesnt mean our database lacks of normlization, but when you process billions of data everyday, not everything CAN be simple.

share|improve this answer

We have a program where I am working that I think is a good case for triggers as there are now about 8+ different versions floating around (an API and many versions of frontends and backends). If I want to make a change in the way that it processes something, it would have been far easier if it were in a trigger rather than to have to make that exact same change in 8+ different code bases (with varrying levels of spaghetti coding and poorly named variables).

share|improve this answer

If you have seven different apps all talking to the users database, wouldn't it make more sense having a stored proc called "createUser" rather than seven different applications building that INSERT statement on their own?

And now, a new app, has to add users to this database, but it has a new requirement, and a new field that needs to be added, that's default value is populated from a value stored in a completely different 3rd party application's database.

Now, you cold go change those seven apps, plus the new one, to talk to the 3rd party app to get the value, while building the INSERT statement.

Or, you can modify the users database's createUser proc to lookup the data from the 3rd party database as a default value, so none of your other programs need to be changed and redeployed, since they don't really care about that value...yet.

Or, you can add a trigger to the users database when the users table is updated, to get that value from the 3rd party database.

Stored procedures also have the benefit of being compiled and therefor quicker than a regular statement.

Stored procedures also can break up a single complex sql statement into several simpler statements to increase the speed at which the query runs.

When data requirements change, it's much simpler to modify a stored proc, than to update 1000s of installations of an application.

My $.02

ps. I haven't written a line of sql in an application in years. It all goes in stored procedures. Be it a simple select, insert, update, a complex report, or an update that is effectively a single object in the application, but stored across 7 different tables in the database.

share|improve this answer
I too have not written a line of sql in an application for years. However, I haven't put any of it in stored procedures. I don't like to drag my application requirements down to the database where they don't belong. I have tools for data access in my applications that will handle pretty much everything I can throw at them, as long as the data structures are normalized and flexible. Using techniques of SOA or deployment tools like ClickOnce, I don't have the 1000s of installations to update either. Since people have solved these problems for me already, I'm well taken care of. –  Travis Heseman Sep 22 '09 at 19:00

Your Answer


