using triggers to enforce data integrity - redundant?

Question

I have a general DB design question.

I have a table in a database where a certain field should always have the value 0 when inserted. Changing this field to any value other than 0 involves certain actions, and these are enforced via update triggers. A record with 0 value without the proper business rules enforced is useless data.

I am contemplating creating an instead-of trigger on insert, to enforce that you can't insert a record into this table with any value other than 0. This is not really necessary, because users can only access the database via stored procs, and the only INSERT proc currently sets this value to the default, which is 0. This would also not be a security measure since in our system only SA is a privileged user, and if a user somehow acquires the SA password, they can do whatever they want anyway.

The only use I can think of for this, is to make sure that even in the future, no developers can accidentally allow inserting values into this field without stumbling on to the trigger, which makes is a testing tool really.

How do DBAs approach something like this? Do you have triggers and constraints specific to testing systems, that do not deploy to production servers? Do you leave these triggers on production servers just as an extra measure of enforcing integrity?

Answer 1

I would be concerned about adding an additional layer of checks.

If you designed your system so all inserts and updates go through stored procedures, then you are saying "there is one place where the logic and data integrity checks occur". If you start spreading the checks across several pieces of code, then you run the risk of confusion. Which checks go into the trigger? Which checks go into the stored procedure? Why is there a trigger when the logic should be in the stored procedure?

I admit that this is a fuzzy boundary. The use of constraints implies that the database is doing some of the checks. However, these are built into the language, so any reasonable developer should be able to easily understand them. In this case, set the default value to 0 for the column, to make it clear even in the table definition that it should initially have this value.

I am a fan of wrapping inserts/updates/deletes in stored procedures, specifically to allow additional checks and logging. The flip side is enforcing the stored procedures as the only interface for modifying tables. You can enforce this by having only one user who is able to modify the data, and having the stored procedure impersonate that user when making changes.

Answer 2

Enforcing a state machine via triggers is a reasonable strategy. A trigger can access both old and new value, and ensure things like "new row must be 0", "0 can transition into 1 or 2, but not 3", etc... Essentially, triggers ensure you cannot make a transition that would be invalid for your state machine.

This can act as an additional layer of "assertions" on top of stored procedures that actually decide which transition to make. Such triggers also tend to be very cheap, so there is no reason to exclude them from production.

Whether all this is justified in your case depends on how complex your state machine is - the more complex it is, the more justified this strategy is.