Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Almost in every interview for the position of web developer, they ask about stored procedures and triggers well I am aware from their definition and basic usage but the truths is I have never used any of them in any of my project. Well I have seen other developers who used them but only when they were the only solution.

My question is are they really important and interview is incomplete without asking questions about triggers and stored procedures. I tend to do keep almost all my logics in PHP, like if change in one database table row will require change in other database table row, I run another query from PHP rather than writing trigger, is it wrong or bad practice? do I need to change my approach? any examples in which use of stored procedures and triggers are better than performing similar operations in PHP?

share|improve this question

marked as duplicate by gnat, Kilian Foth, GlenH7, Yusubov, MichaelT Sep 4 '13 at 12:41

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

@gnat may be a possible duplicate... but I was interested to know the cases in which stored procedures or triggers are the only option and I was also curious to know why they are part of most of interviews .... thanks – Umair Abid Sep 4 '13 at 6:42
@gnat: It's a bit weird to say something that a question is duplicate of something that was itself closed as duplicate. – Jan Hudec Sep 4 '13 at 6:42
@JanHudec weird or not, but I think that's how "signpost" feature is supposed to work. You don't indiscriminately close to some one-and-only "master dupe" but instead pick a signpost that looks better tailored for particular question. Also, it's worth mentioning that in new model, duplicates aren't really "transitive" – gnat Sep 4 '13 at 7:15
up vote 4 down vote accepted

Well I have seen other developers who used them but only when they were the only solution.

Stored procedures and queries are never the only solution. No one forces you to enforce consistency in the database. Hell, you don't even have to be using database in the first place. Or can be using one that does not have either feature.

But there are many situations where they provide some useful benefits:

  1. Security. Many database engines can grant permission to run a stored procedure without granting general permissions to the underlying tables. Even more so using triggers to write history records (what was changed when and by whom, also called audit trail) has the big advantage that even if the application is compromised, the attackers won't be able disable the logging.

  2. Complicated consistency rules. Sometimes you have useful integrity constraints that can't be expressed by simple foreign key, unique and similar constraints. Than it's time to check them with trigger.

  3. Consistency between multiple applications. When you have complex query that needs to be done consistently by different clients, you can put it in stored procedure.

  4. Shielding multiple clients from changes in the schema. If you have database accessed by multiple clients, than if you need to change the schema for one of them, stored procedures and views can hide change from the others so you don't have to update them immediately.

There are however some disadvantages, especially to stored procedures:

  1. They make the code difficult to understand. To understand code processing a query you need to know the query and the query rarely makes sense without the code around it that processes it. For very complex schemas sometimes you can put a simpler layer of views and stored procedures on it, but definitely avoid policy of placing everything in stored procedures.

  2. They are pain the the backside to version. Versioning application code is well mastered, but handling the database schema requires either writing lots of upgrade scripts and some machinery to apply them or lot of error prone manual work. Since triggers and stored procedures are subject to this as well, having many of them increases the work.

share|improve this answer
+1 for the top bits, -1 for the disadvantages. Versioning SPs is easy, they're just code. And they have the advantage in that you can make schema changes (which is the tricky bit to upgrades) and deploy a new SP at the same time, no need to worry that your client SQL and your DB schema is out of sync anymore. – gbjbaanb Sep 4 '13 at 8:00
@gbjbaanb: They are just code, but they don't live in text files. – Jan Hudec Sep 4 '13 at 8:02
They are also a ROYAL PAIN to debug, troubleshoot and test. (BTW they can and should be versioned, you just have to script them all as drop/create scripts and only ever work on the files and run them in, not on the procs themselves inside the DB). – LachlanB Nov 13 '13 at 23:16

I like using stored procedures when using MSSQL, because in MSSQL, you can assign user permissions only to stored procedure. My limited experience with MySQL and Postgres show that stored procedures are still a good practice, but permissions must always be granted on tables used by procedure.

Basically, I use stored procedures to separate SQL syntax from the project itself. You know, get rid of all SELECT statements in code. It is also easier to change a stored procedure than it is to locate the correct SQL statement in code.

As for triggers, we are abusing them at current gig to log data on insert and update to log tables (company policy, not my idea). Otherwise, I would advise to stay away from them, unless they are absolutely necessary.

share|improve this answer
Separating SQL syntax from the project itself actually tends to hurt readability quite a bit as the code usually makes little sense without also seeing the query and vice versa. – Jan Hudec Sep 4 '13 at 6:50
Depends on the name of stored procedure, I guess. – Vladimir Kocjancic Sep 4 '13 at 7:06
Sometimes you can layer a simpler layer of views and stored procedures over complex schema. But selling it as general rule is wrong. It sometimes helps, but often one needs to look up the definition anyway. – Jan Hudec Sep 4 '13 at 7:33
@JanHudec not really - its like saying "using a web service tends to hurt readability as the code makes little sense without seeing the logic".. a SP provides an API into your DB tables. That can easily make things more readable. – gbjbaanb Sep 4 '13 at 7:58
@gbjbaanb: if it can actually provide a decent abstraction. I've mainly seen the cases where it can't. – Jan Hudec Sep 4 '13 at 8:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.