Require an explicit value in an UPDATE statement?

Is there a way, using a trigger or some other means, to ensure that a particular field is always explicitly set when a row is updated, even when running ad hoc S开发者_运维问答QL via SQL Server Management Studio? Specifically, our tables all have change_note fields, and we always want that field to be set for every update. Things we've considered and discarded:

1. Set change_note to NOT NULL. We do this, but it doesn't help. Once change_note has an initial value, you can run as many UPDATE statements as you want that forget to update change_note and NOT NULL remains happy because the previous value is still sitting in change_note.

2. Use a trigger to compare the old version of change_note to the new value of change_note. Sometimes you want change_note to be the same as it was previously. It's not that we want to ensure that change_note changes, we want to ensure that a value for it is explicitly provided in any update.

3. Deny permissions for running ad hoc SQL and force everybody to only update data through our stored procedure API, and make change_note a required param for all SPs that update the relevant tables. We have too many ad hoc querying needs for this.

Stumped! Any ideas?

Thanks!

You can use the Update() function within the trigger body. It will check if the statement which fired the trigger tried to change the value on the column:

if Update(column)
    --do whay you want;BEGIN....END;

You can find more information about it in:

http://msdn.microsoft.com/en-us/library/ms187326.aspx

http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

A trigger would do the job (make sure you write it to handle multiple record inserts/updates). However, your first and second points seem mutually exclusive to me. How would you know which ones need to be changed if not null is not good enough. Do you have some sort of business rule?

Use #2 and handle the "sometimes" part within your update trigger.

Require all users to execute changes by inserting the updated values into another table (where change_note is NOT NULL), and use triggers to copy the values over?

see this post: https://stackoverflow.com/questions/6896834/is-possible-to-restrict-the-db2-database-operators-must-using-where-clause-in and it's comments and the post it links to.