-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html
27 lines (27 loc) · 29.9 KB
/
sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!DOCTYPE html><html lang=en prefix="og: https://ogp.me/ns#"><head><meta charset=utf-8><base href=https://blog.georgekosmidis.net/sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html><meta name=viewport content="width=device-width, initial-scale=1"><meta name=description content="I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use a simple app I wrote, that just opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new."><meta name=author content="George Kosmidis"><meta http-equiv=Content-Security-Policy content="script-src 'nonce-fc0cbc39-3c5d-4ed9-824e-538366c81ad4'"><link rel=me type=text/html href=https://github.com/georgekosmidis><link rel=me type=text/html href="https://www.linkedin.com/in/georgekosmidis/"><link rel=me type=text/html href=https://www.youtube.com/c/GeorgeKosmidis><link rel=me type=text/html href="https://sessionize.com/georgekosmidis/"><link rel=me type=text/html href="https://www.meetup.com/munich-dotnet-meetup/members/202480733/profile/"><link rel=me type=text/html href="https://georgekosmidis.net/"><link rel=icon type=image/jpg href=/media/me_180x180.jpg><link rel=apple-touch-icon type=image/jpg sizes=180x180 href=/media/me_180x180.jpg><meta property=og:title content="SQL Server: How to simulate DROP TRIGGER IF EXISTS"><meta property=og:type content=article><meta property=og:url content=https://blog.georgekosmidis.net/sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html><meta property="og:description " content="I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use a simple app I wrote, that just opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new."><meta property=og:article:published_time content="04/26/2013 15:38:00"><meta property=og:article:modified_time content="12/26/2021 16:16:35"><meta property=og:article:expiration_time content="12/31/9999 23:59:59"><meta property=og:article:author content="George Kosmidis"><meta property=og:article:section content=""><meta property=og:article:tag content=" MSSQL Drop, MSSQL Function, MSSQL Stored Procedure, MSSQL Trigger, SQL Server"><link href=https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css rel=stylesheet integrity=sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3 crossorigin=anonymous><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/cookieconsent@3.1.1/build/cookieconsent.min.css integrity="sha256-zQ0LblD/Af8vOppw18+2anxsuaz3pWYyVWi+bTvTH8Q=" crossorigin=anonymous><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/@highlightjs/cdn-assets@11.4.0/styles/vs2015.min.css integrity="sha256-Pi771++jBrwgeHVYGOa1sjN8idXlrrYSKQVI7+JA54k=" crossorigin=anonymous><title>SQL Server: How to simulate DROP TRIGGER IF EXISTS</title><style>header{background-image:url(/media/header_bg.jpg);background-size:cover;background-position:100% 100%}header svg{width:25px;fill:#f8f9fa}footer svg{width:25px;fill:black}.me{width:180px;height:180px}.article{overflow-wrap:break-word;word-wrap:break-word;-ms-word-break:break-all;word-break:break-all;word-break:break-word}.article img{max-width:100%;height:auto}h1,h2,h3,h4,h5,h6{font-weight:400}h2{margin-top:4rem}.right-column-container h2,.index-card-wrapper h2{margin-top:0}.h3,h3{margin-top:3rem;font-size:1.65rem}.h4,h4{font-size:1.4eem}.h5,h5{font-size:1.2eem}.h4,.h5,h4,h5{margin-top:2rem}th,strong,b{font-weight:500}a{text-decoration:none}a:hover{text-decoration:underline}a.btn svg{margin-bottom:3px}.article-google-engine-top,.article-google-engine-left{display:none}@media (min-width:992px){.container{max-width:1200px}header > .container{max-width:768px}.article-google-engine-left{display:block}td{padding:10px}}@media (max-width:991.98px){.container{padding-right:calc(var(--bs-gutter-x) * .3);padding-left:calc(var(--bs-gutter-x) * .3)}.article-google-engine-top{display:block}td{padding:5px 2px 5px 2px}}blockquote{text-rendering:optimizelegibility;font-weight:400;line-height:160%;box-sizing:inherit;background-color:#DEEBFF;outline-color:#171717;color:#171717;word-wrap:break-word;word-break:break-word;border:1px solid #DEEBFF;border-radius:.375rem;margin-top:1rem;padding:1rem;font-size:1rem;transition:height .5s ease-in,opacity .5s ease-in;display:block;position:relative}blockquote p{margin-bottom:0}dfn{border-color:#343a40}</style><script async src="https://www.googletagmanager.com/gtag/js?id=UA-3071108-41" nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><script nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments);}
gtag('js',new Date());gtag('config','UA-3071108-41');</script></head><body class=bg-light><header class="container-fluid p-3 bg-dark border-bottom"> <div class="container text-white"> <div class=row> <div class="col-sm-12 col-md-3 text-center"> <a href="/"> <img src=/media/me_180x180.jpg class="rounded-circle border border-white border-5 me" alt="George Kosmidis - I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use a simple app I wrote, that just opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new."> </a> </div> <div class="col-sm-12 col-md-9"> <div class=container> <div class=row> <div class=col> <h1 class="display-5 text-center">George Kosmidis</h1> <small class="d-block text-center mt-3">Microsoft MVP | Speaks of Azure, AI & .NET | Founder of Munich .NET <br> Building tomorrow @<div style=display:none>slalom</div> <img src=/media/slalom_small.png title=slalom alt=slalom></small> </div> </div> <div class="row mt-3"> <div class="col text-center"> <a href=https://github.com/georgekosmidis target=_blank class=btn rel=noopener> <svg role=img class=icon viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>GitHub</title><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg> </a> <a href="https://www.linkedin.com/in/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>LinkedIn</title><path d="M20.447 20.452h-3.554v-5.569c0-1.328-.027-3.037-1.852-3.037-1.853 0-2.136 1.445-2.136 2.939v5.667H9.351V9h3.414v1.561h.046c.477-.9 1.637-1.85 3.37-1.85 3.601 0 4.267 2.37 4.267 5.455v6.286zM5.337 7.433c-1.144 0-2.063-.926-2.063-2.065 0-1.138.92-2.063 2.063-2.063 1.14 0 2.064.925 2.064 2.063 0 1.139-.925 2.065-2.064 2.065zm1.782 13.019H3.555V9h3.564v11.452zM22.225 0H1.771C.792 0 0 .774 0 1.729v20.542C0 23.227.792 24 1.771 24h20.451C23.2 24 24 23.227 24 22.271V1.729C24 .774 23.2 0 22.222 0h.003z"></path></svg> </a> <a href=https://www.youtube.com/c/GeorgeKosmidis target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>YouTube</title><path d="M23.498 6.186a3.016 3.016 0 0 0-2.122-2.136C19.505 3.545 12 3.545 12 3.545s-7.505 0-9.377.505A3.017 3.017 0 0 0 .502 6.186C0 8.07 0 12 0 12s0 3.93.502 5.814a3.016 3.016 0 0 0 2.122 2.136c1.871.505 9.376.505 9.376.505s7.505 0 9.377-.505a3.015 3.015 0 0 0 2.122-2.136C24 15.93 24 12 24 12s0-3.93-.502-5.814zM9.545 15.568V8.432L15.818 12l-6.273 3.568z"></path></svg> </a> <a href="https://sessionize.com/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 288 288" xmlns=http://www.w3.org/2000/svg><title>Sessionize</title><g transform="translate(0.000000,288.000000) scale(0.100000,-0.100000)"><path d="M135 2861 c-22 -10 -54 -34 -72 -52 -67 -71 -63 22 -63 -1369 0 -1391 -4 -1298 63 -1369 70 -73 42 -71 715 -71 l602 0 0 40 c0 108 -52 232 -127 301 -92 85 -229 125 -469 139 -119 6 -160 13 -190 28 -63 32 -96 81 -108 161 -6 43 12 87 470 1118 263 590 480 1073 483 1073 3 0 22 -42 41 -92 47 -127 157 -344 228 -453 156 -239 367 -450 607 -608 100 -65 373 -202 478 -238 37 -13 67 -27 67 -30 0 -3 -348 -160 -772 -349 -425 -188 -777 -346 -782 -350 -4 -5 11 -18 35 -30 235 -119 367 -331 394 -633 l7 -77 482 0 c444 0 484 2 521 19 51 23 111 89 125 138 8 27 10 249 8 773 -4 660 -6 743 -22 809 -69 289 -197 522 -396 721 -199 199 -432 327 -721 396 -67 16 -148 18 -819 21 -712 3 -747 2 -785 -16z m2069 -483 c99 -51 198 -181 200 -264 1 -57 -21 -79 -77 -78 -137 3 -328 220 -288 328 19 50 84 55 165 14z"></path></g></svg> </a> <a href="https://www.meetup.com/munich-dotnet-meetup/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>Meetup</title><path d="M6.98.555a.518.518 0 0 0-.105.011.53.53 0 1 0 .222 1.04.533.533 0 0 0 .409-.633.531.531 0 0 0-.526-.418zm6.455.638a.984.984 0 0 0-.514.143.99.99 0 1 0 1.02 1.699.99.99 0 0 0 .34-1.36.992.992 0 0 0-.846-.482zm-3.03 2.236a5.029 5.029 0 0 0-4.668 3.248 3.33 3.33 0 0 0-1.46.551 3.374 3.374 0 0 0-.94 4.562 3.634 3.634 0 0 0-.605 4.649 3.603 3.603 0 0 0 2.465 1.597c.018.732.238 1.466.686 2.114a3.9 3.9 0 0 0 5.423.992c.068-.047.12-.106.184-.157.987.881 2.47 1.026 3.607.24a2.91 2.91 0 0 0 1.162-1.69 4.238 4.238 0 0 0 2.584-.739 4.274 4.274 0 0 0 1.19-5.789 2.466 2.466 0 0 0 .433-3.308 2.448 2.448 0 0 0-1.316-.934 4.436 4.436 0 0 0-.776-2.873 4.467 4.467 0 0 0-5.195-1.656 5.106 5.106 0 0 0-2.773-.807zm-5.603.817a.759.759 0 0 0-.423.135.758.758 0 1 0 .863 1.248.757.757 0 0 0 .193-1.055.758.758 0 0 0-.633-.328zm15.994 2.37a.842.842 0 0 0-.47.151.845.845 0 1 0 1.175.215.845.845 0 0 0-.705-.365zm-8.15 1.028c.063 0 .124.005.182.014a.901.901 0 0 1 .45.187c.169.134.273.241.432.393.24.227.414.089.534.02.208-.122.369-.219.984-.208.633.011 1.363.237 1.514 1.317.168 1.199-1.966 4.289-1.817 5.722.106 1.01 1.815.299 1.96 1.22.186 1.198-2.136.753-2.667.493-.832-.408-1.337-1.34-1.12-2.26.16-.688 1.7-3.498 1.757-3.93.059-.44-.177-.476-.324-.484-.19-.01-.34.081-.526.362-.169.255-2.082 4.085-2.248 4.398-.296.56-.67.694-1.044.674-.548-.029-.798-.32-.72-.848.047-.31 1.26-3.049 1.323-3.476.039-.265-.013-.546-.275-.68-.263-.135-.572.07-.664.227-.128.215-1.848 4.706-2.032 5.038-.316.576-.65.76-1.152.784-1.186.056-2.065-.92-1.678-2.116.173-.532 1.316-4.571 1.895-5.599.389-.69 1.468-1.216 2.217-.892.387.167.925.437 1.084.507.366.163.759-.277.913-.412.155-.134.302-.276.49-.357.142-.06.343-.095.532-.094zm10.88 2.057a.468.468 0 0 0-.093.011.467.467 0 0 0-.36.555.47.47 0 0 0 .557.36.47.47 0 0 0 .36-.557.47.47 0 0 0-.464-.37zm-22.518.81a.997.997 0 0 0-.832.434 1 1 0 1 0 1.39-.258 1 1 0 0 0-.558-.176zm21.294 2.094a.635.635 0 0 0-.127.013.627.627 0 0 0-.48.746.628.628 0 0 0 .746.483.628.628 0 0 0 .482-.746.63.63 0 0 0-.621-.496zm-18.24 6.097a.453.453 0 0 0-.092.012.464.464 0 1 0 .195.908.464.464 0 0 0 .356-.553.465.465 0 0 0-.459-.367zm13.675 1.55a1.044 1.044 0 0 0-.583.187 1.047 1.047 0 1 0 1.456.265 1.044 1.044 0 0 0-.873-.451zM11.4 22.154a.643.643 0 0 0-.36.115.646.646 0 0 0-.164.899.646.646 0 0 0 .899.164.646.646 0 0 0 .164-.898.646.646 0 0 0-.54-.28z"></path></svg> </a> </div> </div> </div> </div> </div> </div> </header> <main class="container bg-light pt-4 pb-5"> <div class="row masonry"> <div class=container> <div class="row mx-auto article-google-engine-top"> <div class="col pb-4"> <div class="card text-center bg-transparent border-0" style=height:33px><script async src="https://cse.google.com/cse.js?cx=c67a1214306f44e87" nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><div class=gcse-searchbox-only></div> </div> </div> </div> <div class="row mx-auto"> <div class="col-lg-8 mb-4 shadow bg-white rounded"> <div class="container article"> <div class="row border-end border-start border-bottom bg-light"> <div class=col style=text-align:center> <a class="btn align-middle" href=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100030-sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists/content.html role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 16 16"><path d="M8 0C3.58 0 0 3.58 0 8c0 3.54 2.29 6.53 5.47 7.59.4.07.55-.17.55-.38 0-.19-.01-.82-.01-1.49-2.01.37-2.53-.49-2.69-.94-.09-.23-.48-.94-.82-1.13-.28-.15-.68-.52-.01-.53.63-.01 1.08.58 1.23.82.72 1.21 1.87.87 2.33.66.07-.52.28-.87.51-1.07-1.78-.2-3.64-.89-3.64-3.95 0-.87.31-1.59.82-2.15-.08-.2-.36-1.02.08-2.12 0 0 .67-.21 2.2.82.64-.18 1.32-.27 2-.27.68 0 1.36.09 2 .27 1.53-1.04 2.2-.82 2.2-.82.44 1.1.16 1.92.08 2.12.51.56.82 1.27.82 2.15 0 3.07-1.87 3.75-3.65 3.95.29.25.54.73.54 1.48 0 1.07-.01 1.93-.01 2.2 0 .21.15.46.55.38A8.012 8.012 0 0 0 16 8c0-4.42-3.58-8-8-8z" /></svg> Edit Page </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net//issues/new?permalink=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100030-sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists/content.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 24 24"><path fill-rule=evenodd d="M2.5 12a9.5 9.5 0 1119 0 9.5 9.5 0 01-19 0zM12 1C5.925 1 1 5.925 1 12s4.925 11 11 11 11-4.925 11-11S18.075 1 12 1zm0 13a2 2 0 100-4 2 2 0 000 4z"></path></svg> Create Issue </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net/discussions/new?title=SQL Server: How to simulate DROP TRIGGER IF EXISTS&body=Article%20URL:%20https://blog.georgekosmidis.net//sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg aria-hidden=true height=22 viewBox="0 0 16 16" width=22><path fill-rule=evenodd d="M1.5 2.75a.25.25 0 01.25-.25h8.5a.25.25 0 01.25.25v5.5a.25.25 0 01-.25.25h-3.5a.75.75 0 00-.53.22L3.5 11.44V9.25a.75.75 0 00-.75-.75h-1a.25.25 0 01-.25-.25v-5.5zM1.75 1A1.75 1.75 0 000 2.75v5.5C0 9.216.784 10 1.75 10H2v1.543a1.457 1.457 0 002.487 1.03L7.061 10h3.189A1.75 1.75 0 0012 8.25v-5.5A1.75 1.75 0 0010.25 1h-8.5zM14.5 4.75a.25.25 0 00-.25-.25h-.5a.75.75 0 110-1.5h.5c.966 0 1.75.784 1.75 1.75v5.5A1.75 1.75 0 0114.25 12H14v1.543a1.457 1.457 0 01-2.487 1.03L9.22 12.28a.75.75 0 111.06-1.06l2.22 2.22v-2.19a.75.75 0 01.75-.75h1a.25.25 0 00.25-.25v-5.5z"></path></svg> Discuss </a> <a class="btn ms-4" href="/" role=button style=text-decoration:none> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 class=mb-1 fill=currentColor class="bi bi-house-fill" viewBox="0 0 16 16"><path fill-rule=evenodd d="m8 3.293 6 6V13.5a1.5 1.5 0 0 1-1.5 1.5h-9A1.5 1.5 0 0 1 2 13.5V9.293l6-6zm5-.793V6l-2-2V2.5a.5.5 0 0 1 .5-.5h1a.5.5 0 0 1 .5.5z"></path><path fill-rule=evenodd d="M7.293 1.5a1 1 0 0 1 1.414 0l6.647 6.646a.5.5 0 0 1-.708.708L8 2.207 1.354 8.854a.5.5 0 1 1-.708-.708L7.293 1.5z"></path></svg> Go Home </a> </div> </div> <div class=row> <div class="col mt-3"> <h1 class="text-center display-6">SQL Server: How to simulate DROP TRIGGER IF EXISTS</h1> </div> </div> <div class=row> <div class="col pb-2"> <div class="text-black-50 text-center"> <small>by <a href="https://georgekosmidis.net/" rel=noopener>George Kosmidis</a> / Published 12 years ago, modified 3 years and 2 months ago</small> </div> </div> </div> <div class=row> <div class="col pt-3 pb-6"> <p>I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use a simple app I wrote, that just opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new.</p> <p></p> <p>Since there is no <code>DROP TRIGGER IF EXISTS</code>, I came up with this:</p> <p><script src=https://gist.github.com/georgekosmidis/5df82554e25ba05da7466824f6d3d352.js></script></p> <p>Of course, ‘YOUR_TRIGGER_NAME’ could be ‘YOUR_FUNCTION_NAME’ or whatever! Just match it with the ‘type’. These are all the possible values of column type:</p> <pre><code class=language-plaintext>
<strong>AF = Aggregate function</strong> (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
<strong>FN = Scalar function</strong>
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued functionIF = In-lined table-function
IT = Internal table
K = PRIMARY KEY or UNIQUE constraint
L = Log
<strong>P = Stored procedure</strong>
PC = Assembly (CLR) stored-procedure
R = Rule
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
<strong>TF = Table function</strong>
<strong>TR = SQL DML Trigger</strong>
TT = Table type
U = User table
<strong>V = View</strong>
X = Extended stored procedure
</code></pre> <p>You can read more about sys.sysobjects here: <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx?WT.mc_id=DT-MVP-5004591" target=_blank rel="noopener noreferrer">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a></p> </div> </div> <div class="row border-end border-start border-top bg-light mt-4"> <div class="col pt-2" style=text-align:center> This page is <strong>open source</strong>. Noticed a typo? Or something unclear?<br> <a class="btn align-middle" href=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100030-sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists/content.html role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 16 16"><path d="M8 0C3.58 0 0 3.58 0 8c0 3.54 2.29 6.53 5.47 7.59.4.07.55-.17.55-.38 0-.19-.01-.82-.01-1.49-2.01.37-2.53-.49-2.69-.94-.09-.23-.48-.94-.82-1.13-.28-.15-.68-.52-.01-.53.63-.01 1.08.58 1.23.82.72 1.21 1.87.87 2.33.66.07-.52.28-.87.51-1.07-1.78-.2-3.64-.89-3.64-3.95 0-.87.31-1.59.82-2.15-.08-.2-.36-1.02.08-2.12 0 0 .67-.21 2.2.82.64-.18 1.32-.27 2-.27.68 0 1.36.09 2 .27 1.53-1.04 2.2-.82 2.2-.82.44 1.1.16 1.92.08 2.12.51.56.82 1.27.82 2.15 0 3.07-1.87 3.75-3.65 3.95.29.25.54.73.54 1.48 0 1.07-.01 1.93-.01 2.2 0 .21.15.46.55.38A8.012 8.012 0 0 0 16 8c0-4.42-3.58-8-8-8z" /></svg> Edit Page </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net//issues/new?permalink=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100030-sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists/content.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 24 24"><path fill-rule=evenodd d="M2.5 12a9.5 9.5 0 1119 0 9.5 9.5 0 01-19 0zM12 1C5.925 1 1 5.925 1 12s4.925 11 11 11 11-4.925 11-11S18.075 1 12 1zm0 13a2 2 0 100-4 2 2 0 000 4z"></path></svg> Create Issue </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net/discussions/new?title=SQL Server: How to simulate DROP TRIGGER IF EXISTS&body=Article%20URL:%20https://blog.georgekosmidis.net//sql-server-how-to-simulate-drop-trigger-or-function-or-sp-if-exists.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg aria-hidden=true height=22 viewBox="0 0 16 16" version=1.1 width=22 data-view-component=true class="octicon octicon-comment-discussion UnderlineNav-octicon d-none d-sm-inline"><path fill-rule=evenodd d="M1.5 2.75a.25.25 0 01.25-.25h8.5a.25.25 0 01.25.25v5.5a.25.25 0 01-.25.25h-3.5a.75.75 0 00-.53.22L3.5 11.44V9.25a.75.75 0 00-.75-.75h-1a.25.25 0 01-.25-.25v-5.5zM1.75 1A1.75 1.75 0 000 2.75v5.5C0 9.216.784 10 1.75 10H2v1.543a1.457 1.457 0 002.487 1.03L7.061 10h3.189A1.75 1.75 0 0012 8.25v-5.5A1.75 1.75 0 0010.25 1h-8.5zM14.5 4.75a.25.25 0 00-.25-.25h-.5a.75.75 0 110-1.5h.5c.966 0 1.75.784 1.75 1.75v5.5A1.75 1.75 0 0114.25 12H14v1.543a1.457 1.457 0 01-2.487 1.03L9.22 12.28a.75.75 0 111.06-1.06l2.22 2.22v-2.19a.75.75 0 01.75-.75h1a.25.25 0 00.25-.25v-5.5z"></path></svg> Discuss </a> </div> </div> </div> </div> <div class=col-lg-4> <div class="container px-0 mx-0 right-column-container"> <div class="col mb-4"> <div class="card text-center bg-transparent border-0" style=height:33px><script async src="https://cse.google.com/cse.js?cx=c67a1214306f44e87" nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><div class=gcse-searchbox-only></div> </div> </div> <div class="col mb-4"> <div class="card shadow"> <img src=/media/mvp.png class="bd-placeholder-img card-img-top" alt="Microsoft MVP - George Kosmidis"> <a href=https://mvp.microsoft.com/en-us/PublicProfile/5004591 target=_blank title="Microsoft MVP - George Kosmidis" class=stretched-link rel=noopener></a> </div> </div> <div class="col mb-4"> <div class="card shadow"> <img src=/media/azure-architecture-icons.png class="bd-placeholder-img card-img-top" alt="Azure Architecture Icons - SVGs, PNGs and draw.io libraries"> <a href=/azure-architecture-icons.html target=_top title="Azure Architecture Icons - SVGs, PNGs and draw.io libraries" class=stretched-link rel=noopener></a> </div> </div> </div> </div> </div> <div class=col-lg-4> </div> </div> </div> </div> </main> <footer class="footer mt-auto py-3 border-top"> <div class="container mx-auto text-center"> <a href=https://github.com/georgekosmidis target=_blank class=btn rel=noopener> <svg role=img class=icon viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>GitHub</title><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg> </a> <a href="https://www.linkedin.com/in/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>LinkedIn</title><path d="M20.447 20.452h-3.554v-5.569c0-1.328-.027-3.037-1.852-3.037-1.853 0-2.136 1.445-2.136 2.939v5.667H9.351V9h3.414v1.561h.046c.477-.9 1.637-1.85 3.37-1.85 3.601 0 4.267 2.37 4.267 5.455v6.286zM5.337 7.433c-1.144 0-2.063-.926-2.063-2.065 0-1.138.92-2.063 2.063-2.063 1.14 0 2.064.925 2.064 2.063 0 1.139-.925 2.065-2.064 2.065zm1.782 13.019H3.555V9h3.564v11.452zM22.225 0H1.771C.792 0 0 .774 0 1.729v20.542C0 23.227.792 24 1.771 24h20.451C23.2 24 24 23.227 24 22.271V1.729C24 .774 23.2 0 22.222 0h.003z"></path></svg> </a> <a href=https://www.youtube.com/c/GeorgeKosmidis target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>YouTube</title><path d="M23.498 6.186a3.016 3.016 0 0 0-2.122-2.136C19.505 3.545 12 3.545 12 3.545s-7.505 0-9.377.505A3.017 3.017 0 0 0 .502 6.186C0 8.07 0 12 0 12s0 3.93.502 5.814a3.016 3.016 0 0 0 2.122 2.136c1.871.505 9.376.505 9.376.505s7.505 0 9.377-.505a3.015 3.015 0 0 0 2.122-2.136C24 15.93 24 12 24 12s0-3.93-.502-5.814zM9.545 15.568V8.432L15.818 12l-6.273 3.568z"></path></svg> </a> <a href="https://sessionize.com/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 288 288" xmlns=http://www.w3.org/2000/svg><title>Sessionize</title><g transform="translate(0.000000,288.000000) scale(0.100000,-0.100000)"><path d="M135 2861 c-22 -10 -54 -34 -72 -52 -67 -71 -63 22 -63 -1369 0 -1391 -4 -1298 63 -1369 70 -73 42 -71 715 -71 l602 0 0 40 c0 108 -52 232 -127 301 -92 85 -229 125 -469 139 -119 6 -160 13 -190 28 -63 32 -96 81 -108 161 -6 43 12 87 470 1118 263 590 480 1073 483 1073 3 0 22 -42 41 -92 47 -127 157 -344 228 -453 156 -239 367 -450 607 -608 100 -65 373 -202 478 -238 37 -13 67 -27 67 -30 0 -3 -348 -160 -772 -349 -425 -188 -777 -346 -782 -350 -4 -5 11 -18 35 -30 235 -119 367 -331 394 -633 l7 -77 482 0 c444 0 484 2 521 19 51 23 111 89 125 138 8 27 10 249 8 773 -4 660 -6 743 -22 809 -69 289 -197 522 -396 721 -199 199 -432 327 -721 396 -67 16 -148 18 -819 21 -712 3 -747 2 -785 -16z m2069 -483 c99 -51 198 -181 200 -264 1 -57 -21 -79 -77 -78 -137 3 -328 220 -288 328 19 50 84 55 165 14z"></path></g></svg> </a> <a href=https://www.facebook.com/groups/msdevtech target=_blank class=btn rel=noopener> <svg xmlns=http://www.w3.org/2000/svg viewBox="0 0 400 400" role=img><path d="M353.701,0H55.087C24.665,0,0.002,24.662,0.002,55.085v298.616c0,30.423,24.662,55.085,55.085,55.085 h147.275l0.251-146.078h-37.951c-4.932,0-8.935-3.988-8.954-8.92l-0.182-47.087c-0.019-4.959,3.996-8.989,8.955-8.989h37.882 v-45.498c0-52.8,32.247-81.55,79.348-81.55h38.65c4.945,0,8.955,4.009,8.955,8.955v39.704c0,4.944-4.007,8.952-8.95,8.955 l-23.719,0.011c-25.615,0-30.575,12.172-30.575,30.035v39.389h56.285c5.363,0,9.524,4.683,8.892,10.009l-5.581,47.087 c-0.534,4.506-4.355,7.901-8.892,7.901h-50.453l-0.251,146.078h87.631c30.422,0,55.084-24.662,55.084-55.084V55.085 C408.786,24.662,384.124,0,353.701,0z" /></svg> </a> <a href="https://www.meetup.com/munich-dotnet-meetup/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>Meetup</title><path d="M6.98.555a.518.518 0 0 0-.105.011.53.53 0 1 0 .222 1.04.533.533 0 0 0 .409-.633.531.531 0 0 0-.526-.418zm6.455.638a.984.984 0 0 0-.514.143.99.99 0 1 0 1.02 1.699.99.99 0 0 0 .34-1.36.992.992 0 0 0-.846-.482zm-3.03 2.236a5.029 5.029 0 0 0-4.668 3.248 3.33 3.33 0 0 0-1.46.551 3.374 3.374 0 0 0-.94 4.562 3.634 3.634 0 0 0-.605 4.649 3.603 3.603 0 0 0 2.465 1.597c.018.732.238 1.466.686 2.114a3.9 3.9 0 0 0 5.423.992c.068-.047.12-.106.184-.157.987.881 2.47 1.026 3.607.24a2.91 2.91 0 0 0 1.162-1.69 4.238 4.238 0 0 0 2.584-.739 4.274 4.274 0 0 0 1.19-5.789 2.466 2.466 0 0 0 .433-3.308 2.448 2.448 0 0 0-1.316-.934 4.436 4.436 0 0 0-.776-2.873 4.467 4.467 0 0 0-5.195-1.656 5.106 5.106 0 0 0-2.773-.807zm-5.603.817a.759.759 0 0 0-.423.135.758.758 0 1 0 .863 1.248.757.757 0 0 0 .193-1.055.758.758 0 0 0-.633-.328zm15.994 2.37a.842.842 0 0 0-.47.151.845.845 0 1 0 1.175.215.845.845 0 0 0-.705-.365zm-8.15 1.028c.063 0 .124.005.182.014a.901.901 0 0 1 .45.187c.169.134.273.241.432.393.24.227.414.089.534.02.208-.122.369-.219.984-.208.633.011 1.363.237 1.514 1.317.168 1.199-1.966 4.289-1.817 5.722.106 1.01 1.815.299 1.96 1.22.186 1.198-2.136.753-2.667.493-.832-.408-1.337-1.34-1.12-2.26.16-.688 1.7-3.498 1.757-3.93.059-.44-.177-.476-.324-.484-.19-.01-.34.081-.526.362-.169.255-2.082 4.085-2.248 4.398-.296.56-.67.694-1.044.674-.548-.029-.798-.32-.72-.848.047-.31 1.26-3.049 1.323-3.476.039-.265-.013-.546-.275-.68-.263-.135-.572.07-.664.227-.128.215-1.848 4.706-2.032 5.038-.316.576-.65.76-1.152.784-1.186.056-2.065-.92-1.678-2.116.173-.532 1.316-4.571 1.895-5.599.389-.69 1.468-1.216 2.217-.892.387.167.925.437 1.084.507.366.163.759-.277.913-.412.155-.134.302-.276.49-.357.142-.06.343-.095.532-.094zm10.88 2.057a.468.468 0 0 0-.093.011.467.467 0 0 0-.36.555.47.47 0 0 0 .557.36.47.47 0 0 0 .36-.557.47.47 0 0 0-.464-.37zm-22.518.81a.997.997 0 0 0-.832.434 1 1 0 1 0 1.39-.258 1 1 0 0 0-.558-.176zm21.294 2.094a.635.635 0 0 0-.127.013.627.627 0 0 0-.48.746.628.628 0 0 0 .746.483.628.628 0 0 0 .482-.746.63.63 0 0 0-.621-.496zm-18.24 6.097a.453.453 0 0 0-.092.012.464.464 0 1 0 .195.908.464.464 0 0 0 .356-.553.465.465 0 0 0-.459-.367zm13.675 1.55a1.044 1.044 0 0 0-.583.187 1.047 1.047 0 1 0 1.456.265 1.044 1.044 0 0 0-.873-.451zM11.4 22.154a.643.643 0 0 0-.36.115.646.646 0 0 0-.164.899.646.646 0 0 0 .899.164.646.646 0 0 0 .164-.898.646.646 0 0 0-.54-.28z"></path></svg> </a> <div class=text-muted>© Copyright 2022, <a href="https://georgekosmidis.net/" target=_blank rel=noopener>George Kosmidis</a> <br> Is lawyer the most boring job in the world? Read the <a href=/privacy.html>Privacy Policy</a> to find out. <br><small class=text-black-50>Last Build: 2025-02-28T03:07:51+00:00 </small> </div> </div> </footer><script src=https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js integrity=sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p crossorigin=anonymous nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><script src=https://cdn.jsdelivr.net/npm/cookieconsent@3.1.1/build/cookieconsent.min.js integrity="sha256-5VhCqFam2Cn+yjw61zbBNrbHVJ6SRydPeKopYlngbiQ=" crossorigin=anonymous nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><script src=https://cdn.jsdelivr.net/npm/@highlightjs/cdn-assets@11.4.0/highlight.min.js integrity="sha256-GCgWKkl4RE3+M/TNH5d/F80Tz30PQT+Oubq5Q3I5c20=" crossorigin=anonymous nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4></script><script nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4>hljs.highlightAll();</script><script nonce=fc0cbc39-3c5d-4ed9-824e-538366c81ad4>window.cookieconsent.initialise({"palette":{"popup":{"background":"#343c66","text":"#cfcfe8"},"button":{"background":"#f71559"}},"showLink":false,"theme":"classic","content":{"message":"Cookie Warning! My blog uses Google Analytics so Google knows you are here. Maybe others too, including FBI, CIA, NASA, ESA, IIS and other acronyms. If you don't like it, you must unfortunately go :(","dismiss":"It's just code, I 'll stay!"}});</script></body></html>