{"id":728,"date":"2010-04-04T10:36:00","date_gmt":"2010-04-04T10:36:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(430)-DDL-triggers-are-INSTEAD-OF-triggers.aspx"},"modified":"2017-07-31T17:18:55","modified_gmt":"2017-08-01T00:18:55","slug":"a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/","title":{"rendered":"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a><\/em> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\"><strong>Myth #4:<\/strong> <em>DDL triggers (introduced in SQL Server 2005) are INSTEAD OF triggers.<\/em><\/p>\n<p style=\"text-align: justify;\"><strong><em><u>FALSE<\/u><\/em><\/strong><\/p>\n<p style=\"text-align: justify;\">DDL triggers are implemented as <em>AFTER<\/em> triggers, which means the operation occurs and is then caught in the trigger (and optionally rolled-back, if you put a <em>ROLLBACK<\/em> statement in the trigger body).<\/p>\n<p style=\"text-align: justify;\">This means they&#8217;re not quite as lightweight as you might think. Imagine doing the following:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER TABLE MyBigTable ADD MyNewNonNullColumn VARCHAR (20) DEFAULT 'Paul';\r\n<\/pre>\n<p style=\"text-align: justify;\">If there&#8217;s a DDL trigger defined for <em>ALTER_TABLE<\/em> events, or maybe even something more restrictive like <em>DDL_TABLE_EVENTS<\/em>, every row in the table will be expanded to include the new column (as it has a non-null default), and then the trigger will fire and the operation is rolled back by your trigger body. Not ideal at all. (Try it yourself and look in the log with fn_dblog &#8211; you&#8217;ll see the operation rollback.)<\/p>\n<p style=\"text-align: justify;\">What would be better in this case is to specifically <em>GRANT<\/em> or <em>DENY<\/em> the <em>ALTER<\/em> permission, or do something like only permitting DDL operations through stored-procedures that you create.<\/p>\n<p style=\"text-align: justify;\">However, DDL triggers do allow you to effectively stop it happening, but in a relatively expensive way. And they do allow you to perform auditing of who did what, so I&#8217;m not saying they&#8217;re without use &#8211; just be careful.<\/p>\n<p style=\"text-align: justify;\">Kimberly has a great post on DDL triggers at <em><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/execute-as-and-an-important-update-your-ddl-triggers-for-auditing-or-prevention\/\" target=\"_blank\" rel=\"noopener noreferrer\">&#8220;EXECUTE AS&#8221; and an important update your DDL Triggers (for auditing or prevention)<\/a><\/em>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #4: DDL triggers (introduced in SQL Server 2005) are [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,79],"tags":[],"class_list":["post-728","post","type-post","status-publish","format-standard","hentry","category-misconceptions","category-security"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #4: DDL triggers (introduced in SQL Server 2005) are [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-04T10:36:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-01T00:18:55+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/\",\"name\":\"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-04T10:36:00+00:00\",\"dateModified\":\"2017-08-01T00:18:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #4: DDL triggers (introduced in SQL Server 2005) are [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-04T10:36:00+00:00","article_modified_time":"2017-08-01T00:18:55+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/","name":"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-04T10:36:00+00:00","dateModified":"2017-08-01T00:18:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-430-ddl-triggers-are-instead-of-triggers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (4\/30) DDL triggers are INSTEAD OF triggers"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=728"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/728\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}