{"id":82111,"date":"2011-06-21T00:36:13","date_gmt":"2011-06-21T00:36:13","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73380"},"modified":"2018-12-12T12:03:59","modified_gmt":"2018-12-12T12:03:59","slug":"chapter-7-enforced-data-protection","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/chapter-7-enforced-data-protection\/","title":{"rendered":"Chapter 7\u2013Enforced Data Protection"},"content":{"rendered":"<p>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 <a title=\"http:\/\/www.sqlsolstice.com\/\" href=\"http:\/\/www.sqlsolstice.com\/\">http:\/\/www.sqlsolstice.com\/<\/a>\u2026 shameless plug, but it is on topic \ud83d\ude42 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\u2026emails are going out once I talk it over with my editor).<\/p>\n<p>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\u2019t be 1\/2 of the book, or so I am told), I will put that off to probably the final chapter.<\/p>\n<p>This chapter was broken up into two main sections, Check Constraints and Triggers.&#160; I will demonstrate the following scenarios, and if you see anything missing, please do make suggestions<\/p>\n<p>Check Constraints<\/p>\n<ul>\n<li>Simple value checks \u2013 Like when you want to make sure there are no blank string values CHECK (len(value) &gt; 0)<\/li>\n<li>Value reasonableness checks \u2013 Like if a value should be a non-negative integer, CHECK (value &gt;= 0)<\/li>\n<li>Checks using different tables \u2013 Like setting up a data driven format check<\/li>\n<\/ul>\n<p>Triggers \u2013 Broken down by AFTER and INSTEAD OF Triggers<\/p>\n<ul>\n<li>AFTER<\/li>\n<ul>\n<li>Range checks on multiple rows \u2013 Like when you want to make sure that the sum of rows related to (and including) the newly inserted rows is &gt; 0<\/li>\n<li>Maintaining summary values (only as necessary) \u2013 Denormalization, pure and simple, but if you are going to do it, triggers are the way to go (you really shouldn\u2019t)<\/li>\n<li>Cascading inserts \u2013 Like creating child rows to ensure a 1 to at least 1 row relationship is met, or creating a parent<\/li>\n<li>Child-to-parent cascades \u2013 Like deleting parent rows when the last child row is deleted<\/li>\n<li>Maintaining an audit trail \u2013 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 <\/li>\n<li>Relationships that span databases and servers \u2013 sometimes you just have to implement RI between databases, so it is back to 6.0 style RI<\/li>\n<\/ul>\n<li>INSTEAD OF<\/li>\n<ul>\n<li>Automatically maintaining values \u2013 For example, if you want to implement a bulletproof rowLastUpdatedTime column to know when the row last changed, but don\u2019t trust the client (who does?)<\/li>\n<li>Formatting user input \u2013 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<\/li>\n<li>Redirecting invalid data to an exception table \u2013 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.<\/li>\n<li>Forcing no action to be performed on a table, even by someone who technically has proper rights \u2013 Simple do nothing trigger that works because in an instead of trigger you have to replicate the action, so you don\u2019t.<\/li>\n<\/ul>\n<\/ul>\n<p>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 \u201cmagical\u201d 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.&#160; 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.<\/p>\n<p>In any case, I do my best to make it clear that you don\u2019t in fact have to do any of this, but here are the tools in the tool bag. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\/\u2026 shameless plug, but it is on topic \ud83d\ude42 I start to find that a given order works better. Originally&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82111","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82111"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82111\/revisions"}],"predecessor-version":[{"id":82269,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82111\/revisions\/82269"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82111"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}