validating data in database: sql vs code

Question

My database schema has a 'locked' setting meaning that the entry can not be changed once it is set.

Before the locked flag is set we can update other attributes. So:

• Would you check the locked flag in code and then update the entry

or

• would it be better to combine that into a SQL query, if so, any examples?

EDIT: how would you combine the update & check into one SQL statement?

Answer 1

I would check the locked flag in code and then (assuming the record isn't locked) run the update query, setting the locked flag in the query as well. That way it can be wrapped in a transaction and committed/rolled back all at once.

Answer 2

I would use a trigger if your DBMS offers this function to enforce the flag. If you set the flag, all updates fail.

Then you can create a special query which will update the flag. Your trigger can check what called the update, and allow the flag to flick back if necessary. That way whether the TSQL is nice or malicious, no one can update your row once the flag is set.

Answer 3

You should do both. The database should use an update trigger to decide if a row can be updated - this would prevent any one updating the row from the back tables accidentally. And the application should check to see if it should be able to update the rows and act accordingly.

Answer 4

As a rule of thumb I would always prefer to check everything in code and consider writing the DB constraints after that. Having the DB perform consistency checks for you is cool and admittedly faster than doing it in your code but then you are putting some logic in the DB and have to pay some price. These constraints can become vendor-dependent and, worse, can you perform automated tests on these restrictions in your development environment?

So I'd consider putting this kind of checks in the DB as an added safety net but wouldn't rely on them alone.

Answer 5

"how would you combine the update & check into one SQL statement?"

update table
set ...
where key = ...
and locked ='N';

That would not raise an error, but would update 0 rows - something you should be able to test for after the update.

As for which is better, my view is that if this locked flag is important then:

• you must check/enforce it in the database to ensure it is never violated by any access method
• you may also check/enforce it in the application, if that is more user-friendly