Importance of stored procedures and triggers in building web applications? [duplicate]

Question

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?

Answer 1

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.

Answer 2

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.