As the book progresses, I find myself veering from the original stated outline quite a bit, because as I teach about this more (and I am teaching a daylong db design class in August at http://www.sqlsolstice.com/… shameless plug, but it is on topic 🙂 I start to find that a given order works better. Originally I had slated myself to talk more about modeling here for three chapters, then get back to the more implementation topics to finish out the book, but now I am going to keep plugging through the implementation tasks, then finish up with modeling task (which I hope I might end up getting some help with…emails are going out once I talk it over with my editor).

In the last edition, the chapter on data protection was more inclusive, including programmatic data protection, including client code and stored procedures. But, keeping with the basic, implementation type chapters (and trying my best to shorten chapters to more realistic chunks (the free chapter shouldn’t be 1/2 of the book, or so I am told), I will put that off to probably the final chapter.

This chapter was broken up into two main sections, Check Constraints and Triggers.  I will demonstrate the following scenarios, and if you see anything missing, please do make suggestions

Check Constraints

• Simple value checks – Like when you want to make sure there are no blank string values CHECK (len(value) > 0)
• Value reasonableness checks – Like if a value should be a non-negative integer, CHECK (value >= 0)
• Checks using different tables – Like setting up a data driven format check

Triggers – Broken down by AFTER and INSTEAD OF Triggers

• AFTER
• Range checks on multiple rows – Like when you want to make sure that the sum of rows related to (and including) the newly inserted rows is > 0
• Maintaining summary values (only as necessary) – Denormalization, pure and simple, but if you are going to do it, triggers are the way to go (you really shouldn’t)
• Cascading inserts – Like creating child rows to ensure a 1 to at least 1 row relationship is met, or creating a parent
• Child-to-parent cascades – Like deleting parent rows when the last child row is deleted
• Maintaining an audit trail – Also something that will come up in security, but implementing an audit trail of actions on a table. Less needed these days with auditing, but
• Relationships that span databases and servers – sometimes you just have to implement RI between databases, so it is back to 6.0 style RI
• INSTEAD OF
• Automatically maintaining values – For example, if you want to implement a bulletproof rowLastUpdatedTime column to know when the row last changed, but don’t trust the client (who does?)
• Formatting user input – Like formatting words in all caps, or proper case. Another thing that might be better done outside of SQL Server, but it is very straightforward to implement
• Redirecting invalid data to an exception table – For example, eliminating data that is outside of the norm. Possibly done better outside of SQL Server code, but if you really want to build something that takes previous data into consideration, this might be a reasonable way.
• Forcing no action to be performed on a table, even by someone who technically has proper rights – Simple do nothing trigger that works because in an instead of trigger you have to replicate the action, so you don’t.

It might seem weird to consider formatting data or redirecting invalid data to another table as data protection, but the point of data protection is to make sure that they data ends up in a reasonable state, and triggers can do some “magical” seeming stuff. Admittedly, triggers are not a fan favorite with many programmers because they do those magical stuff that they cannot directly control, but in many ways that is the point.  If the dev forgets to update the last update date, the ETL may not see the row, and oops your data is out of sync.

In any case, I do my best to make it clear that you don’t in fact have to do any of this, but here are the tools in the tool bag.