{"id":82032,"date":"2008-07-13T19:16:53","date_gmt":"2008-07-13T19:16:53","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73191"},"modified":"2019-05-22T09:26:33","modified_gmt":"2019-05-22T09:26:33","slug":"triggers-evil","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/triggers-evil\/","title":{"rendered":"Triggers…Evil?"},"content":{"rendered":"

Say it isn’t so. “It isn’t so.”\u00a0 Glenn Berry thinks so in his post here<\/a>. When I read his post I thought I was going to really get into it with Conor’s post here<\/a>, called the Trouble with Triggers (a title which I had to post because I like Star Trek too,) but frankly I agree with Conor.\u00a0 Triggers are great tools, when applied correctly.\u00a0 When they are used in a bad way (which is quite often, from the code I have seen), well, agree with Glenn I must…<\/p>\n

In my book, I advocate triggers for a few things:<\/p>\n

* Cross-database referential integrity (RI)<\/i>: Just basic RI, but SQL Server doesn\u2019t manage declarative constraints across database boundaries.<\/p>\n

* Intra-table, inter-row constraints<\/i>: For example, when you need to see that the sum of a column value over multiple rows is less than some value (possibly in another table).<\/p>\n

* Inter-table constraints<\/i>: For example, if a value in one table relies on the value in another. This might also be written as a functions-based CHECK constraint, but it is often more maintainable to use a trigger.<\/p>\n

* Introducing desired side effects to your queries<\/i>: For example, cascading inserts, maintaining denormalized data, and so on.<\/p>\n

(and I give examples of each.) but I also am careful to say to be extremely careful with them. When my tech reviewer got to the start of my section on triggers, he quickly said that he didn’t like them and they shouldn’t be used.\u00a0 By the end he agreed with my examples.\u00a0 The problem with triggers is just like the problem with several tools that SQL Server gives you.\u00a0 For example:<\/p>\n

* Stored procedures:<\/em> If used to encapsulate set based SQL calls into precompiled batches…good.\u00a0 Used as a functional language to do work row by row, often with cursors…not so good.\u00a0 Used to implement lots of business rules…well, that depends on the business rules but often this is where the real trouble comes (that is a future post)<\/p>\n

* Cursors<\/em>: Used to do some repetitive task, usually for some maintenance use…good.\u00a0 Used in place of set based operations because the programmer cannot write good SQL…baaaddd (said properly should sound like a bleating sheep.)<\/p>\n

* Clustered indexes<\/em>: Used to cluster on the right sort of key (like a small monotonically increasing value)…good.\u00a0 Used to cluster on a random value, like a guid…not so good.\u00a0 Never used as a search argument of any kind, forcing bookmark lookups constantly….well, yuck.<\/p>\n

So just like you don’t hammer in a nail with a wrench, or hammer in a screw with any implement, triggers are not to be used improperly.\u00a0 The problem is that of education.\u00a0 There are so many people out there who just do what it is they feel like without regards for what is actually correct.\u00a0 SQL programmers try to do everything in SQL (I have been guilty of this many times) and functional programmers try to do everything one operation at a time.\u00a0 Some day, once we all trust the SQLCLR to the extent we ought to, perhaps the exact balance can be achieved.<\/p>\n

And Glenn, I completely agree with your reasons to hate triggers: “I witnessed first hand the consequences of over-use of DML triggers in a large, n-tier client\/server application.”\u00a0 I think the important thing in there is the word “over-use.”\u00a0 I mean, a person could probably get fat eating only celery and drinking water if they over did that.<\/p>\n","protected":false},"excerpt":{"rendered":"

Say it isn’t so. “It isn’t so.”\u00a0 Glenn Berry thinks so in his post here. When I read his post I thought I was going to really get into it with Conor’s post here, called the Trouble with Triggers (a title which I had to post because I like Star Trek too,) but frankly I……<\/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-82032","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\/82032","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=82032"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82032\/revisions"}],"predecessor-version":[{"id":84352,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82032\/revisions\/84352"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82032"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}