Working without stored procedures or triggers

Question

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?

EDIT:

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!"

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

"How many triggers are set to make some fancy updates between table fields, or to recalculate totals or other aggregated data?"

Using a trigger to do complex updates based on business rules is not a flaw. It is the prefered method. All business rules should be enforced at the datbase level if you want to maintain data integrity. There areways 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 functinoality will have to follow the rules (instead of having remeber 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 becasue they have to calcuate 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 orginal design to the the new one (it takes quite some time and a diffenrent set of skills to build an OLAP database).

Answer 8

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.

Answer 9

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).

Answer 10

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.