Share via


Using triggers to protect your database from developers - still not a good idea

A long time ago I mentioned that triggers is rarely a good option in your database. I recently read this article (requires free registration) that shows you a solution to prevent developers from updating a full table (or deleting it all) by mistake because they failed to ad a WHERE clause to their query. Is this a good reason to add triggers to your database? I think not! There are so many things wrong here in my opinion...

First of all the script proposed the example have the same trigger duplicated three times (once for update, delete and update/delete triggers each) when that could have been avoided easily since the script is just building a string to run anyway. So from the start the script proposed could easily contain bugs depending on the type of trigger added.

Second this is only protecting against the most simple mistakes where you forget your where clause. What if a query has a where clause like "WHERE ID > 1" (assuming there is at least one record not satisfying that condition. You are probably still miserable.

I would still say that the use of stored procedures and proper security setup so that users can only execute stored procedures and not modify data is the solution. That way the stored procedure can be reviewed. Even checked automatically for things like always having a where clause. The stored procedure can also be tested with some unit tests. All that should be enough and better than adding triggers that gives you some false sense of protection. And remember your backups in the case you failed to catch something like this earlier. But that goes without saying whenever you work with a database I think.