SQL table design for some columns protected

Question

I have a question about the best practice / best solution for a table design where most of the columns have to be write protected after a certain condition is met (lock_date is set).

Imagine the following table setup. A folder table and a document table with around 15 columns and one of them is a lock date.

folder table COLUMNS id, ...

document table COLUMNS fk_folder, lock_date, col_1, col_2, col_3, ....

So after the lock date is set, the first 10 cols on the document table should be protected against changes.

I currently have two solution for this:

1. Create two tables out of it which one holds the none changeable information. And secure the first one with a trigger. (Where should the fk_folder go??)

2. Create a trigger for the whole table which check all the cols which should NOT be updated.

With the second approach I see a problem when new columns are added. Because it's very easy to forget that the trigger may need to be adjusted to include the new columns.

Are there any better solutions?

Answer 1

IMHO, you should use neither of the solutions. Your problem is a business logic problem and should NOT be put in the database, this will cause your business logic to be scattered in multiple layers. You should handle the protection of locked columns in application level. So for every Update you perform in application, check if the lock_date has been set (not null), then prevent changes.