source.by

Audit Trail Generator for Microsoft SQL - CodeProject

Source 2020: https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414 | 2020-02-09

        <!DOCTYPE HTML>


<html lang="en">
<head>
	<title>Audit Trail Generator for Microsoft SQL - CodeProject</title> 
    
	<link type="text/css" rel="stylesheet" href="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Css/Main.min.css?dt=2.8.200223.1">

    <script type="text/javascript" src="https://www.codeproject.com/script/JS/jquery-2.2.4.min.js" defer ></script>
<script type="text/javascript" src="https://codeproject.freetls.fastly.net/script/Articles/JS/article.min.js?dt=2.8.200223.1" defer ></script>
<script type="text/javascript" src="/script/JS/advertisement.min.js" defer ></script>
<script type="text/javascript" src="/script/Membership/JS/MemberProfilePopup.min.js?v=1.4" defer ></script>
<script type="text/javascript" src="/a.min.js?v=1.3"></script>

	
<meta http-equiv="content-language" content="en-US">
<meta charset="UTF-8">
<meta name="Description" content="Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database">
<meta name="Keywords" content="C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,Database,Database,Free source code, tutorials">
<meta name="Author" content="Cedric Baelemans">
<meta name="Rating" content="General">
<meta name="Revisit-After" content="1 days">
<meta name="application-name" content="CodeProject">
<meta name="google-translate-customization" content="d908bb7ce7aff658-4c2f3a504525c916-g629383f736781a8a-13">

<link rel="dns-prefetch" href="//ajax.googleapis.com"> 
<link rel="canonical" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL" />
<meta name="twitter: card" content="summary" />
<meta name="twitter: site" content="@CodeProject" />
<meta property="og:url" content="https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL" />
<meta property="og:title" content="Audit Trail Generator for Microsoft SQL" />
<meta property="og:description" content="Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database" />
<meta property="og:image" content="https://www.codeproject.com/KB/database/AuditTrailGenerator/Thumb-21068.jpg" />

<link rel="alternate" type="application/rss+xml" title="CodeProject Latest articles - All Topics" href="https://www.codeproject.com/WebServices/ArticleRSS.aspx?cat=1" />
<link rel="alternate" type="application/rss+xml" title="CodeProject Latest articles - Artificial Intelligence" href="https://www.codeproject.com/WebServices/ArticleRSS.aspx?cat=31" />
<link rel="alternate" type="application/rss+xml" title="CodeProject Lounge Postings" href="https://www.codeproject.com/webservices/LoungeRSS.aspx" />
<meta name="robots" content="index, follow" />
<link rel="search" type="application/opensearchdescription+xml" title="CodeProject" href="https://www.codeproject.com/info/OpenSearch.xml">
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<link rel="apple-touch-icon" sizes="144x144" href="/favicon/apple-touch-icon.png"> 
<link rel="icon" type="image/png" sizes="32x32" href="/favicon/favicon-32x32.png"> 
<link rel="icon" type="image/png" sizes="16x16" href="/favicon/favicon-16x16.png"> 
<link rel="manifest" href="/favicon/manifest.json"> 
<link rel="mask-icon" href="/favicon/safari-pinned-tab.svg" color="#ff9900">
	<script type='application/ld+json'>{
  "@context": "http://schema.org",
  "@type": "TechArticle",
  "mainEntityOfPage": {
    "@type": "WebPage",
    "@id": "https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL"
   },
  "name": "Audit Trail Generator for Microsoft SQL",
  "headline": "Audit Trail Generator for Microsoft SQL",
  "url": "https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL",
  "discussionUrl": "https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL#_comments",
  "isFamilyFriendly": "true",
  "image": "https://www.codeproject.com/KB/database/AuditTrailGenerator/Thumb-21068.jpg",
  "keywords": "C#,SQL,Windows,.NET,SQL-Server,Visual-Studio,Architect,DBA,Dev,Design,Intermediate",
  "commentCount": "64",
  "editor" : {
    "@type" : "Person",
    "name" : "Deeksha Shenoy",
    "url" : "https://www.codeproject.com/script/Membership/View.aspx?mid=3866010"
  },
  "publisher" : {
    "@type" : "Organization",
    "name" : "CodeProject"
  },
  "description": "Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database",
  "articleSection": "Database",
  "author" : [{
      "@type" : "Person",
      "name" : "Cedric Baelemans",
      "url" : "https://www.codeproject.com/script/Membership/View.aspx?mid=671546"
    }],
  "datePublished": "2007-10-27",
  "dateCreated": "2007-10-27",
  "dateModified": "2007-10-27"
,
  "aggregateRating" : {
    "@type" : "AggregateRating",
    "ratingValue" : 4.94,
    "ratingCount" : 54,
    "bestRating" : 5,
    "worstRating" : 1
  }
}</script>

<script type='application/ld+json'>{
  "@context": "http://schema.org",
  "@type": "BreadcrumbList",
  "itemListElement": [{
    "@type": "ListItem",
    "position": 1,
    "item" : {
      "@id" : "/Chapters/4/Database.aspx",
      "name" : "Database"
    }
  },{
    "@type": "ListItem",
    "position": 2,
    "item" : {
      "@id" : "/KB/database/",
      "name" : "Database"
    }
  }]
}</script>


	<base target="_top" />
	
    


<script type='text/javascript'>
function defrm () { /* thanks twitter */
    document.write = '';
    window.top.location = window.self.location;
    setTimeout(function() { document.body.innerHTML = ''; }, 0);
    window.self.onload = function(evt) { document.body.innerHTML = ''; };
}

if (window.top !== window.self) {
    try {
        if (window.top.location.host) { /* will throw for all except chrome */ }
        else { defrm(); /* chrome */ }
    } catch (ex) { defrm(); /* everyone else */ }
}

// Specific case where a site is screwing with us.
if (typeof(DemoUrl) !== 'undefined') {
    document.write(unescape('%3Cme') + 'ta http' + '-equiv="re' + 'fresh con' +
                           'tent="1;url=' + DemoUrl + unescape('"%3CE'));
}
</script>
	





<!-- Global site tag (gtag.js) - Google Analytics -->
    <script async type="text/javascript" src="https://www.googletagmanager.com/gtag/js?id=UA-1735123-1"></script>
    <script type="text/javascript">
        window.dataLayer = window.dataLayer || [];
        function gtag() { dataLayer.push(arguments); }
        gtag('js', new Date());

        gtag('config', 'UA-1735123-1' );
    </script>


</head>	

<body class="safari mobile safari mobile12 mobile">

<a class="access-link" href="#Main"><img alt="Click here to Skip to main content" src="https://codeproject.freetls.fastly.net/images/t.gif" /></a>




<div class="page-background">

	
	

	

	
    <div id="ctl00_STM" class="site-top-menu fixed">
        <div class="main-content">
            

<div class="container memberbar clearfix flex-container flex-extend">

	<div id="ctl00_MemberBar_GenInfo" class="flex-item align-left">14.5M <img src="/script/Membership/Images/unknown_sm.gif"></div>

	<div class="flex-item">
		
	</div>

	<div class="flex-item align-right">

		

		

		

			<script type="text/javascript">//<!--
			function doSubmit(secure)
			{
				if (secure)
					document.signinForm.action = "https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414"
				else
					document.signinForm.action = "https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414"
				document.signinForm.submit();
				return true;
			}//-->
			</script>

			<a name="SignUp"></a>
			<span class="member-signin tooltip openable">
				<span><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414">Sign in</a>
                    <img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/login32.png"
                        width="16px" height="16px" style="vertical-align:middle;"/>
				</span>

				<div class="tooltip-flyout">
					<form name="signinForm" id="signinForm" action="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414" method="post"
						class="tight">

						
						<input id="FormName" name="FormName" value="MenuBarForm" type="hidden" />

						<div>Email</div>
						<div><input class="small-text" type="email" name="Email" id="Email" autocomplete="email" /></div>
						<div>Password</div>
						<div><input class="small-text" type="password" name="Password" id="Password" 
							autocomplete="current-password" /></div>
						<div class="action">
                            <input id='joinBtn' type='button' class='create hidden' onclick='return Join();' value='Join' />
                            <input type='hidden' name='fld_quicksign' value='true' />
							<input type="submit" value="Sign in" class="signin" onclick="return doSubmit(false);" />
						</div>

						<div class="container">
							
							 
							<a id="ctl00_MemberBar_SendPassword" class="forgot float-right" href="/script/Membership/SendPassword.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Forgot your password?</a>
						</div>
					</form>

					<hr class="divider-dark" />

					Sign in with <a class="oauth" alt="Sign in using Github" title="Sign in using Github" href="/script/Membership/OAuthLogOn.aspx?auth=GitHub"><img src="/script/Membership/Images/octicons_github.png" style="vertical-align:middle;padding-right:3px;border:0;" /></a>
<a class="oauth" alt="Sign in using Linkedin" title="Sign in using Linkedin" href="/script/Membership/OAuthLogOn.aspx?auth=LinkedIn"><img src="/script/Membership/Images/linkedin.png" style="vertical-align:middle;padding-right:3px;border:0;" /></a>
<a class="oauth" alt="Sign in using Facebook" title="Sign in using Facebook" href="/script/Membership/OAuthLogOn.aspx?auth=Facebook"><img src="/script/Membership/Images/facebook.png" style="vertical-align:middle;padding-right:3px;border:0;" /></a>
<a class="oauth" alt="Sign in using Google" title="Sign in using Google" href="/script/Membership/OAuthLogOn.aspx?auth=Google"><img src="/script/Membership/Images/google-plus.png" style="vertical-align:middle;padding-right:3px;border:0;" /></a>
<a class="oauth" alt="Sign in using Microsoft" title="Sign in using Microsoft" href="/script/Membership/OAuthLogOn.aspx?auth=Microsoft"><img src="/script/Membership/Images/microsoft.png" style="vertical-align:middle;padding-right:3px;border:0;" /></a>

				</div>
            </span>
		
	</div>
</div>
        </div>
    </div>

	
    <div id="ctl00_SH" class="site-header fixed">
        <div class="main-content">
            <div class="logo"><a href="/"><img id="ctl00_Logo" tabindex="1" title="CodeProject" src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/logo250x135.gif" alt="Home" style="height:135px;width:250px;border-width:0px;" /></a></div>
            <div class="promo"><div class="msg-728x90" data-format="728x90" data-type="ad" data-publisher="lqm.codeproject.site" data-zone="Database/Database/SQL-Server"  data-country='US' data-tags='C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,rating4.5'><noscript><a href="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=728x90&c=910031"><img src="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=728x90&c=910031"  width="728px" height="90px" target="_blank"/></a></noscript></div></div>
        </div>
    </div>

	
	<div id="ctl00_TPR" class="sub-headerbar clearfix fixed">
    <div class="float-right">
		

<div class="searchbar">

<form method="get" action="/search.aspx?sbo=kw" name="Search" class="tight">


<table border="0" cellspacing="0" cellpadding="0" class="search"><tr><td><input TabIndex="2" class="search " id="sb_tb" autocomplete="off" value="" name="q" /></td><td><input type="image" src="https://codeproject.freetls.fastly.net/images/search.gif" /></td></tr></table>

<div class="hover-container">
	<div id="SearchFilter" class="search-advanced popup small-text align-left">
	<b>Search within:<br /></b>
		
		<input type="radio" id="sb_kw" name="sbo" value="kw" checked="checked"><label for="sb_kw">Articles</label><br>
<input type="radio" id="sb_qa" name="sbo" value="qa"><label for="sb_qa">Quick Answers</label><br>
<input type="radio" id="sb_fm" name="sbo" value="fm"><label for="sb_fm">Messages</label><br>


		
		

		
		
	</div>
</div>
</form>

</div>
    </div>
    <div class="float-left">
		

<div class="navbar clearfix">
<ul class="navmenu openable">

<li class="desktop-only">


<li class="openable"><a id="ctl00_TopNavBar_Art" class="down selected" href="/script/Articles/Latest.aspx">articles</a>

</li>



<li class="openable">
<a id="ctl00_TopNavBar_Answers" href="/script/Answers/List.aspx?tab=active">
    <span class="desktop-only tablet-only">quick answers</span><span class="mobile-only">Q&A</span>
</a>
</li>



<li class="openable"><a id="ctl00_TopNavBar_Forums" href="/script/Forums/List.aspx">
        <span class="desktop-only tablet-only">discussions</span><span class="mobile-only">forums</span>
</a>

</li>

<li class="openable"><a id="ctl00_TopNavBar_Features" href="/Feature/">
      <span class="desktop-only tablet-only">features</span><span class="mobile-only">stuff</span>
</a>

</li>


<li  class="openable"><a id="ctl00_TopNavBar_Lounge" href="/Lounge.aspx">
            <span class="desktop-only tablet-only">community</span><span class="mobile-only">lounge</span>
</a>

</li>


<li class="openable" style="margin-left:20px"><a id="ctl00_TopNavBar_Help" href="/KB/FAQs/">
    <span class="desktop-only tablet-only">help</span><span class="mobile-only">?</span>
</a>


</li>

</ul>

</div>
    </div>
	<div class="sub-headerbar-divider"></div>
	</div>		
	

	<div id="A" class="container-content-wrap fixed"> 

	<div class="container-content">

        
		<div class="clearfix">
			<div class="container-breadcrumb float-left ">
				<div><a href="/script/Content/SiteMap.aspx">Articles</a> » <a href="/Chapters/4/Database.aspx">Database</a> » <a href="/KB/database/">Database</a> » <a href="/KB/database/#SQL+Server">SQL Server</a></div>
			</div>

            <div class="float-left">
				
			</div>

			<div class="edit-links float-right">
				



 
 










			</div>

			<div class="article-nav float-right">
                
				
			</div>
		</div>

        
		<div class="extended article-container-parts">

			
			<div id="AT" class="article-container  fixed"> 

				<div class="article">

					<form name="aspnetForm" method="post" action="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414" id="aspnetForm" style="margin:0;padding:0">
<div>
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="qypCQXgwLt/ePlQ2SmbOsyQdpHlYEmLoawEh7/GkxFU69Hjl2THH+owrI7pLb3+W2X32oHwIHss8PBZXGgH7l7M0+g/W+wbJOlTZ3Uy4RmrReIqTuZJpGPDjtkVHWiWLqhe37YbVI9q1rTtEJDB2TU57Cot5b5baqm/4O8gvd3kfhNFEBhkH54G5EswrmzOfv0XAe3lF5aM/WbA7vE3FG7jt7rEpXwjQkASCfMe+DRfy76KZ1rkyNtt2HUfCDep42OHv4E/Nk4efmCEWupQ0Dq6jqjcCn7GmknHGGc1fWelaPwe/+efELMr9ZuUodj0h1wO48aIOjHpO7tYe+BUaP62G3AH0JqitTTc2cnwwEzY8fP8LD8dyc1anQv61o/YpQNRtIhNqWz7vyWQ97ssmplXmHeYM56xj13yhjxsv1ZdHPv/NHieSrW2ko4wkoPa9tSbQU0KRDQkcdTYAGGIBdDnAHlfJnCYuZS2kBKT3cJBJraCNKb9lOw2MJ8wTbqavMSkd7bBlbjP22tBF5gOa6NuGXgJx35Rqy2BMEKDdjakn8du2zwwqoNO6C2fDaV+O18QVQlxGuFZl6cUOl34aTnBL6GKZmtlmk6XdfPYL2Zt3r/tKjjSsrRlOY5gkRUFYZbv4LAD1W1kD9mgmQyjzCh4lMCCA8x2v0R8o4WgVUfTn3atMuVeOXxnEK8tVwlkrQP9iBwsR44HpJqR43T30qvIISm+C4hWDSBYLO8nwlDZamQQWcSaLkvZmBV80QGRAeQr4EDQ56PaUrGMgJ46i9aKEbGAYBb1U4GZE7gtP6Nhpbd1PyRPhyft7nUCE2qvfPwm27Eys1PS40X85LVLfVjpuV1R/xshfISV+uDLOOoWxq5zphG4+rpMRmbLkroenDMqheg0LON/RTw0lc1bNpJ0VC4ksLpdUlHN2AygDMbJF38jWyeg6Ec+OX0m1WDSfJp+eyhDW8TSG1MOO8MKAWQt2EVfQdboRyMhUebv8yWC2pLwWItRpLsNEJWZ6au3o+ahcwbeJP8XsRDsOvAkNPiIQioQK+BORvrwvVtc4UACQPDqPznc8EFvxJkoLEE+jGla4P18icF6z3CmZJRd4QtnT1EqOwOvQHWEURA9vAJacHmRC6LjsmOjSf8PgPPUdVZzMoD3DvtczLGU9iGQxcjCg0NGsuq7b64Cv0OIA7zbM++H5pXxIWjmi5XI4mqXs5MfwES/V9aZnToby5MoJLPQgsBoT0S8VdOUpOOvdvcTJK/l1MP6Ude3G3FOQr4QHqv39YiEmtwhhN5M2m43blFmUTJfXYQRjpGegaZptij5yodobDxFASktN0VsMY3/YoCtJsbmVTqqKAjR178ec01mH//pA1+dSd7kP9y7D6g58/sab8MHymybgdf08jtA7c3V5ooJcdfG7Vp16DIRLnOFt6+LZZ1aGT0oEtPRFbWbBrrZX3N7exKIcFQg8ffRl9uCr602TevSwYPgUnKCkcuSOH6jXgbdQBB3AbSZ6LPp4FQB8665nKMMXXYezjYTFtiwo63+PapAj1jWsNT/ty671nZ932DGWlzVl+4izlkwnd/CO" />
</div>

<div>

	<input type="hidden" name="__VIEWSTATEGENERATOR" id="__VIEWSTATEGENERATOR" value="10C1FD69" />
</div>

					
					 
					<div class="header">
					    <a name="Main"></a>

					    
					    <a name="_articleTop" id="_articleTop"></a>
					    <div class="title">
					        <h1 id="ctl00_ArticleTitle">Audit Trail Generator for Microsoft SQL</h1>
					    </div>

                        <div>
					        
					        <div class="entry flex-container">

                                

                                <div class="flex-item" style="flex:1 1 auto">
                                    <div class="flex-container" style="justify-content:space-between;flex-wrap:wrap-reverse">
                                       <span id="ctl00_Authors" class="author flex-item"><a href="/script/Membership/View.aspx?mid=671546" rel="author">Cedric Baelemans</a></span> 

                                        <div class="flex-item" style="margin-top:-7px;height:35px">
                                            <div id="ctl00_RateArticle_RateItemWrapper" class="container-rating small-text" name="RateItem_21068">

	<table width="100%" cellpadding="0" cellspacing="0" class="small-text">
	<tr>
		<td id="ctl00_RateArticle_VoteResultDiv" class="rating-result align-right">
			<span class="align-right"></span>
			<img class="loading invisible" width="16px" alt="loading..." height="16px" 
				src="/images/loading16.gif" /> 
		</td>

	
		<td class="voteTbl" style="white-space:nowrap" align="right">
			<table class="small-text">
			<tr>
				<td id="ctl00_RateArticle_RateText" class="rating-prompt">
					Rate this:
				</td>

				
				<td id="ctl00_RateArticle_VoteFormDiv" class="nowrap tooltip rating-stars-voter-large" style="padding-top:1px">

                    
					

<meta itemprop="upvoteCount" content="51">


<table id="ctl00_RateArticle_ItemRating_RatingTable" cellpadding="0" cellspacing="0" style="margin:0 1px;" class="hide-on-hover float-left">
	<tr>
		<td class="nowrap">

        <div  style="width:120px;height:28px;position:relative" class="nowrap rating-stars-large"><div style="width:118px;height:24px;" class="clipped align-left float-left"><img src="https://codeproject.freetls.fastly.net/script/Ratings/Images/stars-fill-lg.png"></div><div style="width:2px;height:24px;position:relative" class="clipped"><img src="https://codeproject.freetls.fastly.net/script/Ratings/Images/stars-empty-lg.png" style="position:absolute;top:0;right:0"></div>

		
	</td>
		<td id="ctl00_RateArticle_ItemRating_VR" class="nowrap">
		<span id="ctl00_RateArticle_ItemRating_VotesR">4.94  (54 votes)</span>
		
	</td>
	</tr>
</table>

<span id="ctl00_RateArticle_ItemRating_RatingText"></span>


                    
					<span id="ctl00_RateArticle_RB" class="ajaxHist radio voting show-on-hover">
						<span id="ctl00_RateArticle_VoteRBL"><input id="ctl00_RateArticle_VoteRBL_0" type="radio" name="ctl00$RateArticle$VoteRBL" value="1" /><input id="ctl00_RateArticle_VoteRBL_1" type="radio" name="ctl00$RateArticle$VoteRBL" value="2" /><input id="ctl00_RateArticle_VoteRBL_2" type="radio" name="ctl00$RateArticle$VoteRBL" value="3" /><input id="ctl00_RateArticle_VoteRBL_3" type="radio" name="ctl00$RateArticle$VoteRBL" value="4" /><input id="ctl00_RateArticle_VoteRBL_4" type="radio" name="ctl00$RateArticle$VoteRBL" value="5" /></span> 
					</span>

                    
					

                    
	                

                    
                    <div id="ctl00_RateArticle_RSU" class="speech-bubble-container-down">
		                <div class="speech-bubble-down">
                            Please <a id="ctl00_RateArticle_SignUp" href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414">Sign up or sign in</a> 
                            to vote.
		                </div>
		                <div class="speech-bubble-pointer-down">
			                <div class="speech-bubble-pointer-down-inner"></div>
		                </div>
	                </div>    

				</td>

				
                <td id="ctl00_RateArticle_VR" class="nowrap show-on-hover tooltip">

		            <span id="ctl00_RateArticle_VotesR">4.94  (54 votes)</span>

                    
					<div id="ctl00_RateArticle_histPHR" class="speech-bubble-container-up-right" style="width:175px;margin-top:24px;right:-15px;top:6px">
						<div class="speech-bubble-up-right">
							<div id="ctl00_RateArticle_HistogramR">

</div>
							<img id="ctl00_RateArticle_LoadingR" class="align-middle" src="/images/loading24.gif" style="border-width:0px;" />
						</div>
						<div class="speech-bubble-pointer-up-right">
							<div class="speech-bubble-pointer-up-right-inner"></div>
						</div>
					</div>
                </td>


				<td style="padding-left:5px">	
					<input type="submit" name="ctl00$RateArticle$SubmitRateBtn" value="Vote!" id="ctl00_RateArticle_SubmitRateBtn" class="button" />
				</td>
			</tr>
			</table>

			
		</td>
	</tr>
	</table>

</div>
                                        </div>
                                    </div>

                                    <div  class="flex-container">
                                        <span id="ctl00_LastUpdated" class="date flex-item-tight" title="Date last updated">27 Oct 2007</span>
                                    </div>
                                </div>
					        </div>

                            

                        </div>

                        <div id="ctl00_DescriptionSpot" class="summary">Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database</div><span id="ctl00_ThumbnailUrl" class="date" content="https://www.codeproject.com/KB/database/AuditTrailGenerator/Thumb-21068.jpg"></span>			

                    </div>
                    
					
					

					

					
					

					
					
					
					

						
					

					

						
						<div id="contentdiv" class="text">
						



<html><head></head><body><ul class="download">
<li><a href="/KB/database/AuditTrailGenerator/AuditTrailGenerator_source.zip">Download source - 1.9 KB</a> </li></ul>
<img height="300" alt="Screenshot - AuditTrailGenerator_ManagementStudio.jpg" src="/KB/database/AuditTrailGenerator/AuditTrailGenerator_ManagementStudio.jpg" width="400" border="0"> 
<h2>Introduction</h2>

<p>There are different reasons for having an audit trail on your data. Some companies have to do it because of legal obligations, such as <a href="http://en.wikipedia.org/wiki/Sarbanes-oxley">Sarbanes-Oxley</a> or <a href="http://en.wikipedia.org/wiki/Fda">FDA</a> regulations. But I also find it very useful for debugging purposes. It shows you what was in your database at any point in time.</p>

<p>In this article I will explain the method I prefer for implementing an audit trail. Next, I will introduce a script to automate the implementation of the audit trail in the database.</p>

<h2>Audit Trail With Shadow Table and Triggers</h2>

<p>So how to implement an audit trail? Different visions exist. The one I prefer is to use a shadow table for each table that exists in the database. Every time a record is inserted, updated or deleted in a table, the record is also inserted in the corresponding shadow table. For inserting the record in the shadow table too, I use triggers on the original table that will fire whenever something happens. Other names you may encounter on the web for shadow tables are archive table or audit table.</p>

<p>Let's make this clear with a small example.</p>
<img height="280" alt="Screenshot - AuditTrailGenerator_diagram.gif" src="data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==" width="484" border="0" data-src="/KB/database/AuditTrailGenerator/AuditTrailGenerator_diagram.gif" class="lazyload" data-sizes="auto" data-srcset="/KB/database/AuditTrailGenerator/AuditTrailGenerator_diagram-r-400.gif 400w, /KB/database/AuditTrailGenerator/AuditTrailGenerator_diagram.gif 484w"> 
<p>On the left side, you see the structure of a table called <code>Users</code> containing three columns. I refer to this table as the base table. On the right, you see the shadow table for this table. The shadow table contains all columns from the <code>Users</code> table, plus some extra columns: </p>

<ul>
<li><code>AuditId</code>: This the primary key of the shadow table. It is an identity field. </li><li><code>AuditAction</code>: This is a one letter code to indicate the kind of operation. Values are <code>I</code>, <code>U</code> or <code>D</code>, for insert, update and delete respectively. </li><li><code>AuditDate</code>: The date and time when the action occurred. The default value is set to <code>getdate()</code>, an SQL function that returns the current date and time. </li><li><code>AuditUser</code>: The user who performed the action. The default value is set to <code>suser_sname()</code>, an SQL function that returns the user name of the user currently connected. </li><li><code>AuditApp</code>: The application that was used. The default value is set to <code>(('App=('+rtrim(isnull(app_name(),'')))+') ')</code>. This allows you to tell which application was used to modify the data, e.g. <code>App=(Microsoft SQL Server Management Studio Express)</code>. </li></ul>

<p>To fill up the shadow table, I define triggers on the <code>Users</code> table. We need three triggers: one for inserts, one for updates, and one for deletes. The code for the insert action is shown below. Those for updates and deletes are similar. If you are new to triggers, see <a href="http://www.codeproject.com/database/SquaredRomis.asp">Brief about Triggers in SQL Server 2000</a> by SquaredRomi. </p>

<pre lang="sql"><span class="code-keyword">CREATE</span> <span class="code-keyword">TRIGGER</span> tr_users_Insert <span class="code-keyword">ON</span> dbo.users
<span class="code-keyword">FOR</span> <span class="code-keyword">INSERT</span> <span class="code-keyword">AS</span> <span class="code-keyword">INSERT</span> <span class="code-keyword">INTO</span> users_shadow(UserId,FirstName,LastName,AuditAction)
<span class="code-keyword">SELECT</span> UserId,FirstName,LastName,<span class="code-string">'</span><span class="code-string">I'</span> <span class="code-keyword">FROM</span> Inserted</pre>

<p>The columns that are filled up by the trigger are only the data columns from the base table (<code>userid</code>, <code>FirstName </code>and <code>LastName</code>) and the <code>AuditAction </code>column. All other columns in the shadow table (<code>AuditId</code>, <code>AuditDate</code>, <code>AuditUser </code>and <code>AuditApp</code>) are filled up by their default value definition.</p>

<p>So what are the strengths and weaknesses of this approach? Let's start with the strengths:</p>

<ul>
<li>It completely separates the current data from the audit trail. The old values are no longer in the base table but in the shadow table. There are no soft deletes, where deleted records are flagged as being deleted instead of being actually deleted. </li><li>It can easily be implemented on existing databases. If originally you did not foresee audit trailing, you can add it afterwards. The only thing you need to do is add the triggers on the base tables and create the shadow table. No changes have to be made to stored procedures or applications working with your database. </li><li>It always triggers. E.g. if you connect to your database through Enterprise Manager and you modify the data by hand, the triggers fire and the shadow table is updated accordingly. </li></ul>

<p>The method also has some drawbacks:</p>

<ul>
<li>The entire record is copied to the shadow table, including the columns that were not changed. In our example, if you change the <code>firstname</code> of a user in the base table, the <code>lastname </code>is also copied to the shadow table although it did not change. Hence, the shadow table will take up more space than strictly needed. </li><li>A trigger cannot be used on all column data types. <code>Text</code>, <code>Ntext</code>, and <code>Image </code>are not supported. The reason is that they are not stored in the record itself. The record only holds a pointer to the data. In SQL 2005, the timestamp is not supported either. </li><li>The number of tables doubles, although I personally don't find this an objection. </li><li>The audit trail is on a table level instead of on an action level. If during a single save operation in your application multiple tables in your database get updated, there is no link between the different transactions that took place on the different tables. The only thing that links them together is that they occurred at (almost) the same moment and by the same user. </li></ul>

<h2>The Audit Trail Generator Script</h2>

<p>If you have 50 tables in your database, adding an audit trail using the method just described means adding another 50 tables and creating 150 triggers. This is why I have created the audit trail generator. It saves time and avoids typo errors. See the link on top of this article to download the code.</p>

<p>The audit trail generator is written as a stored procedure. Hence, you don't need any other tools. RichardRoe used <a href="http://www.codeproject.com/database/AuditTriggers.asp">codesmith as a generator</a>, but this means you have to buy this tool first.</p>

<p>The stored procedure takes four arguments:</p>

<ul>
<li><code>@TableName</code>: The name of the table to which you want to add an audit trail, e.g. <code>users</code> </li><li><code>@Owner</code>: The owner of the table. The default value is <code>dbo</code></li><li><code>@AuditNameExtention</code>: The extension you want for the shadow table name. E.g., if you set it to <code>_shadow</code>, the audit table for <code>users</code> will be called <code>users_shadow</code>. The default value is <code>_shadow</code> </li><li><code>@DropAuditTable</code>: A bit to specify if the shadow table can be dropped. If <code>1</code>, the existing audit table will be dropped and recreated. Of course, you lose all data in there. This is especially useful when you are still in development, but you may want to do this on a production system. The default value is <code>0</code>. </li></ul>

<p>The stored procedure will discover the columns in the original table by querying the system tables of SQL Server. These system tables are used by SQL Server itself to store the structure of the tables. The query to get all info about the table is shown below. For a complete overview of the system tables in SQL Server, see the <a href="http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx">Microsoft MSDN site</a>.</p>

<pre lang="sql"><span class="code-keyword">SELECT</span> b.name, c.name <span class="code-keyword">as</span> TypeName, b.length, _
    b.isnullable, b.collation, b.xprec, b.xscale
<span class="code-keyword">FROM</span> sysobjects a
<span class="code-keyword">inner</span> <span class="code-keyword">join</span> syscolumns b <span class="code-keyword">on</span> a.id = b.id
<span class="code-keyword">inner</span> <span class="code-keyword">join</span> systypes c <span class="code-keyword">on</span> b.xtype = c.xtype <span class="code-keyword">and</span> c.name <> <span class="code-string">'</span><span class="code-string">sysname'</span>
<span class="code-keyword">WHERE</span> a.id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>)
<span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(a.id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>
<span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span> b.colId</pre>

<p>The image below shows the results if we launch this query for our <code>Users</code> table.</p>
<img height="80" alt="Screenshot - AuditTrailGenerator_QueryResult.gif" src="data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==" width="539" border="0" data-src="/KB/database/AuditTrailGenerator/AuditTrailGenerator_QueryResult.gif" class="lazyload" data-sizes="auto" data-srcset="/KB/database/AuditTrailGenerator/AuditTrailGenerator_QueryResult-r-400.gif 400w, /KB/database/AuditTrailGenerator/AuditTrailGenerator_QueryResult.gif 539w"> 
<p>The remainder of the stored procedure loops over the result of this query using a cursor, and dynamically builds up the SQL statements in a string to create the shadow table and to add the triggers to the original table. These statements are then executed with the <code>EXEC</code> command. I will not go into the details of it, since it is straight forward.</p>

<h2>Using the Script</h2>

<p>The script is a stored procedure, so using it means calling the stored procedure. In its simplest form, you only need to set the <code>@TableName </code>parameter because for all other parameters, default values have been specified. The following statement can be launched from a query window.</p>

<pre lang="sql"><span class="code-keyword">EXECUTE</span> GenerateAudittrail <span class="code-string">'</span><span class="code-string">Users'</span></pre>

<p>The following example shows what it looks like if all parameter values are specified.</p>

<pre lang="sql"><span class="code-keyword">EXECUTE</span> GenerateAudittrail <span class="code-string">'</span><span class="code-string">Users'</span>, <span class="code-string">'</span><span class="code-string">dbo'</span>,<span class="code-string">'</span><span class="code-string">_shadow'</span>, <span class="code-digit">0</span></pre>

<p>The script is very handy to quickly create a shadow table for a given database table. However, it was not designed to modify a shadow table to reflect changes to the corresponding base table. In this case, it can only drop the shadow table, losing all records in it, and recreate it. Set the <code>@DropAuditTable</code> to <code>1</code> to force dropping and recreating the shadow table.</p>

<h2>Related Articles</h2>

<p>There are two articles I would like to mention that relate to this subject.</p>

<p>Nigel Rivett wrote an <a href="http://www.nigelrivett.net/AuditTrailTrigger.html">article</a> describing an audit trail mechanism similar to the one described here, but where he tries to tackle some of the drawbacks I described. He uses one central audit trail table for all tables in the database, instead of a separate shadow table for each original table. Of course, it avoids the many shadow tables but he has to opt for <code>varchar</code>s to store all values, regardless of what type they are in the original tables. But the most interesting point he makes is the logic that he builds into the triggers. The old values and the new values are compared and only the changed columns are stored in the audit trail. This can save storage space as the entire record is not duplicated. There is only one caveat. The time needed to modify data in a table increases since the trigger, which fires as a result of this modification, will take longer to complete since it contains much more logic compared to the simple triggers created by the audit trail generator presented here.</p>

<p>Ben Allfree criticizes the audit trail approach I took here in <a href="/KB/database/LisRecordVersioning.asp">Record Versioning with SQL Server</a> and proposes an alternative. Unfortunately, he does not mention triggers and moves audit trail logic into the code that accesses his database, such as stored procedures. Hence, it is not possible to add an audit trail to an existing database with the method he proposes, without changing the applications accessing that database. Another thing I dislike is the soft delete approach he takes, flagging old versions of records as inactive instead of actually deleting them. By moving older versions of records to a separate shadow table, I kept the original table clean and the number of records low, without impacting the speed of <code>select </code>statements.</p></body></html>


						</div>
						

						<div class="float-right" style="margin:20px 0 0 10px;border:1px solid #ccc">
						<div class="msg-300x250" data-format="300x250" data-type="ad" data-publisher="lqm.codeproject.site" data-zone="Database/Database/SQL-Server"  data-country='US' data-loadOnView='true'  data-tags='C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,rating4.5'><noscript><a href="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=300x250&c=910031"><img src="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=300x250&c=910031"  width="300px" height="250px" target="_blank"/></a></noscript></div>
						</div>
                        
                        
						
						<h2>License</h2>
						<div id="LicenseTerms"><p>This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.</p><p>A list of licenses authors might use can be found <a href="/info/Licenses.aspx">here</a></p></div>
						<h2>Share</h2>
				        <div class="share-list">
					        

    <ul class="rrssb-buttons clearfix">
        <li id="ctl00_SocialLike_Email" class="email compact">
            <a href="mailto:?subject=Audit%20Trail%20Generator%20for%20Microsoft%20SQL%20-%20CodeProject&body=Here%27s%20an%20interesting%20article%20on%20for%20you%20on%20codeproject.com%0a%0aDiscussion%20of%20audit%20trail%20techniques%20and%20a%20script%20to%20automate%20audit%20trail%20implementation%20on%20a%20Microsoft%20SQL%20database%0a%0ahttps%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414%0a">
                <span class="icon">
                    <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" 
                        version="1.1" x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" 
                        enable-background="new 0 0 28 28" xml:space="preserve">
                        <g>
                            <path d="M20.111 26.147c-2.336 1.051-4.361 1.401-7.125 1.401c-6.462 0-12.146-4.633-12.146-12.265 
                                c0-7.94 5.762-14.833 14.561-14.833c6.853 0 11.8 4.7 11.8 11.252c0 5.684-3.194 9.265-7.399 
                                9.3 c-1.829 0-3.153-0.934-3.347-2.997h-0.077c-1.208 1.986-2.96 2.997-5.023 2.997c-2.532 
                                0-4.361-1.868-4.361-5.062 c0-4.749 3.504-9.071 9.111-9.071c1.713 0 3.7 0.4 4.6 0.973l-1.169 
                                7.203c-0.388 2.298-0.116 3.3 1 3.4 c1.673 0 3.773-2.102 3.773-6.58c0-5.061-3.27-8.994-9.303-8.994c-5.957 
                                0-11.175 4.673-11.175 12.1 c0 6.5 4.2 10.2 10 10.201c1.986 0 4.089-0.43 5.646-1.245L20.111 
                                26.147z M16.646 10.1 c-0.311-0.078-0.701-0.155-1.207-0.155c-2.571 0-4.595 2.53-4.595 5.529c0 
                                1.5 0.7 2.4 1.9 2.4 c1.441 0 2.959-1.828 3.311-4.087L16.646 10.068z"></path>
                        </g>
                    </svg>
                </span>
                
            </a>
        </li><li id="ctl00_SocialLike_Twitter" class="twitter compact">
            <a href="http://twitter.com/home?status=Audit+Trail+Generator+for+Microsoft+SQL+-+CodeProject+-+https%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414" class="popup">
                <span class="icon">
                    <svg version="1.1" id="Layer_1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" 
                        x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" enable-background="new 0 0 28 28" xml:space="preserve">
                    <path d="M24.253,8.756C24.689,17.08,18.297,24.182,9.97,24.62c-3.122,0.162-6.219-0.646-8.861-2.32
                        c2.703,0.179,5.376-0.648,7.508-2.321c-2.072-0.247-3.818-1.661-4.489-3.638c0.801,0.128,1.62,0.076,2.399-0.155
                        C4.045,15.72,2.215,13.6,2.115,11.077c0.688,0.275,1.426,0.407,2.168,0.386c-2.135-1.65-2.729-4.621-1.394-6.965
                        C5.575,7.816,9.54,9.84,13.803,10.071c-0.842-2.739,0.694-5.64,3.434-6.482c2.018-0.623,4.212,0.044,5.546,1.683
                        c1.186-0.213,2.318-0.662,3.329-1.317c-0.385,1.256-1.247,2.312-2.399,2.942c1.048-0.106,2.069-0.394,3.019-0.851
                        C26.275,7.229,25.39,8.196,24.253,8.756z"></path>
                    </svg>
                </span>
                
            </a>
        </li><li id="ctl00_SocialLike_Facebook" class="facebook compact">
            
            <a href="https://www.facebook.com/sharer/sharer.php?u=https%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414" class="popup">
                <span class="icon">
                    <svg version="1.1" id="Layer_1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" 
                        x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" enable-background="new 0 0 28 28" xml:space="preserve">
                        <path d="M27.825,4.783c0-2.427-2.182-4.608-4.608-4.608H4.783c-2.422,0-4.608,2.182-4.608,4.608v18.434
                            c0,2.427,2.181,4.608,4.608,4.608H14V17.379h-3.379v-4.608H14v-1.795c0-3.089,2.335-5.885,5.192-5.885h3.718v4.608h-3.726
                            c-0.408,0-0.884,0.492-0.884,1.236v1.836h4.609v4.608h-4.609v10.446h4.916c2.422,0,4.608-2.188,4.608-4.608V4.783z"></path>
                    </svg>
                </span>
                
            </a>
        </li><li id="ctl00_SocialLike_LinkedIn" class="linkedin compact">
            <a href="http://www.linkedin.com/shareArticle?mini=true&url=https%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414&title=Audit+Trail+Generator+for+Microsoft+SQL+-+CodeProject&summary=Discussion+of+audit+trail+techniques+and+a+script+to+automate+audit+trail+implementation+on+a+Microsoft+SQL+database&source=codeproject.com" class="popup">
                <span class="icon">
                    <svg version="1.1" id="Layer_1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" 
                        x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" enable-background="new 0 0 28 28" xml:space="preserve">
                        <path d="M25.424,15.887v8.447h-4.896v-7.882c0-1.979-0.709-3.331-2.48-3.331c-1.354,0-2.158,0.911-2.514,1.803
                            c-0.129,0.315-0.162,0.753-0.162,1.194v8.216h-4.899c0,0,0.066-13.349,0-14.731h4.899v2.088c-0.01,0.016-0.023,0.032-0.033,0.048
                            h0.033V11.69c0.65-1.002,1.812-2.435,4.414-2.435C23.008,9.254,25.424,11.361,25.424,15.887z M5.348,2.501
                            c-1.676,0-2.772,1.092-2.772,2.539c0,1.421,1.066,2.538,2.717,2.546h0.032c1.709,0,2.771-1.132,2.771-2.546
                            C8.054,3.593,7.019,2.501,5.343,2.501H5.348z M2.867,24.334h4.897V9.603H2.867V24.334z"></path>
                    </svg>
                </span>
                
            </a>
        </li><li id="ctl00_SocialLike_Reddit" class="reddit compact">
            <a href="http://www.reddit.com/submit?url=https%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414&title=Audit+Trail+Generator+for+Microsoft+SQL+-+CodeProject">
                <span class="icon">
                    <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" version="1.1" 
                        x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" 
                        enable-background="new 0 0 28 28" xml:space="preserve"><g>
                    <path d="M11.794 15.316c0-1.029-0.835-1.895-1.866-1.895c-1.03 0-1.893 0.865-1.893 1.895s0.863 1.9 1.9 1.9 C10.958 17.2 11.8 16.3 11.8 15.316z"></path>
                    <path d="M18.1 13.422c-1.029 0-1.895 0.864-1.895 1.895c0 1 0.9 1.9 1.9 1.865c1.031 0 1.869-0.836 1.869-1.865 C19.969 14.3 19.1 13.4 18.1 13.422z"></path>
                    <path d="M17.527 19.791c-0.678 0.678-1.826 1.006-3.514 1.006c-0.004 0-0.009 0-0.014 0c-0.004 0-0.01 0-0.015 0 
                    c-1.686 0-2.834-0.328-3.51-1.005c-0.264-0.265-0.693-0.265-0.958 0c-0.264 0.265-0.264 0.7 0 1 c0.943 0.9 2.4 1.4 
                    4.5 1.402c0.005 0 0 0 0 0c0.005 0 0 0 0 0c2.066 0 3.527-0.459 4.47-1.402 c0.265-0.264 0.265-0.693 0.002-0.958C18.221 
                    19.5 17.8 19.5 17.5 19.791z"></path><path d="M27.707 13.267c0-1.785-1.453-3.237-3.236-3.237c-0.793 0-1.518 0.287-2.082 
                    0.761c-2.039-1.295-4.646-2.069-7.438-2.219 l1.483-4.691l4.062 0.956c0.071 1.4 1.3 2.6 2.7 2.555c1.488 0 2.695-1.208 
                    2.695-2.695C25.881 3.2 24.7 2 23.2 2 c-1.059 0-1.979 0.616-2.42 1.508l-4.633-1.091c-0.344-0.081-0.693 0.118-0.803 
                    0.455l-1.793 5.7 C10.548 8.6 7.7 9.4 5.6 10.75C5.006 10.3 4.3 10 3.5 10.029c-1.785 0-3.237 1.452-3.237 3.2 c0 1.1 0.6 
                    2.1 1.4 2.69c-0.04 0.272-0.061 0.551-0.061 0.831c0 2.3 1.3 4.4 3.7 5.9 c2.299 1.5 5.3 2.3 8.6 2.325c3.228 0 6.271-0.825 
                    8.571-2.325c2.387-1.56 3.7-3.66 3.7-5.917 c0-0.26-0.016-0.514-0.051-0.768C27.088 15.5 27.7 14.4 27.7 13.267z M23.186 
                    3.355c0.74 0 1.3 0.6 1.3 1.3 c0 0.738-0.6 1.34-1.34 1.34s-1.342-0.602-1.342-1.34C21.844 4 22.4 3.4 23.2 3.355z M1.648 
                    13.3 c0-1.038 0.844-1.882 1.882-1.882c0.31 0 0.6 0.1 0.9 0.209c-1.049 0.868-1.813 1.861-2.26 2.9 C1.832 14.2 1.6 13.8 1.6 
                    13.267z M21.773 21.57c-2.082 1.357-4.863 2.105-7.831 2.105c-2.967 0-5.747-0.748-7.828-2.105 
                    c-1.991-1.301-3.088-3-3.088-4.782c0-1.784 1.097-3.484 3.088-4.784c2.081-1.358 4.861-2.106 7.828-2.106 c2.967 0 5.7 
                    0.7 7.8 2.106c1.99 1.3 3.1 3 3.1 4.784C24.859 18.6 23.8 20.3 21.8 21.57z M25.787 14.6 
                    c-0.432-1.084-1.191-2.095-2.244-2.977c0.273-0.156 0.59-0.245 0.928-0.245c1.035 0 1.9 0.8 1.9 1.9 
                    C26.354 13.8 26.1 14.3 25.8 14.605z"></path></g></svg>
                </span>
                
            </a>
        </li><li id="ctl00_SocialLike_Pinterest" class="pinterest compact">
            <a href="http://pinterest.com/pin/create/bookmarklet/?is_video=false&url=https%3a%2f%2fwww.codeproject.com%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3fmsg%3d4167414&description=Audit+Trail+Generator+for+Microsoft+SQL+-+CodeProject">
                <span class="icon">
                    <svg version="1.1" id="Layer_1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" 
                        x="0px" y="0px" width="28px" height="28px" viewBox="0 0 28 28" enable-background="new 0 0 28 28" xml:space="preserve">
                    <path d="M14.021,1.57C6.96,1.57,1.236,7.293,1.236,14.355c0,7.062,5.724,12.785,12.785,12.785c7.061,0,12.785-5.725,12.785-12.785
                        C26.807,7.294,21.082,1.57,14.021,1.57z M15.261,18.655c-1.161-0.09-1.649-0.666-2.559-1.219c-0.501,2.626-1.113,5.145-2.925,6.458
                        c-0.559-3.971,0.822-6.951,1.462-10.116c-1.093-1.84,0.132-5.545,2.438-4.632c2.837,1.123-2.458,6.842,1.099,7.557
                        c3.711,0.744,5.227-6.439,2.925-8.775c-3.325-3.374-9.678-0.077-8.897,4.754c0.19,1.178,1.408,1.538,0.489,3.168
                        C7.165,15.378,6.53,13.7,6.611,11.462c0.131-3.662,3.291-6.227,6.46-6.582c4.007-0.448,7.771,1.474,8.29,5.239
                        c0.579,4.255-1.816,8.865-6.102,8.533L15.261,18.655z"></path>
                    </svg>
                </span>
                
            </a>
        </li>

    </ul>



				        </div> 
    			        
                        <h2 id="ctl00_AboutHeading">About the Author</h2>
						    

<div class="author-wrapper">

    <div class="pic-wrapper"> 
        <img id="ctl00_AboutAuthorRptr_ctl00_AboutAuthor_memberPhoto" class="profile-pic" src="https://codeproject.freetls.fastly.net/script/Membership/Images/member_unknown.gif" style="border-width:0px;transform:rotate(-2deg);" />
    </div>

    <div class="container-member">  
        <b><a id="ctl00_AboutAuthorRptr_ctl00_AboutAuthor_memberProfileLink" class="author" href="/Members/Cedric-Baelemans">Cedric Baelemans</a></b>

        <table>
        <tr>
            <td rowspan="2" nowrap valign="middle">
            
            </td>
            <td nowrap="nowrap">
                <div class="company">
                    <span id="ctl00_AboutAuthorRptr_ctl00_AboutAuthor_memberJobTitle"></span>
	                <span id="ctl00_AboutAuthorRptr_ctl00_AboutAuthor_memberCompany"></span>
                </div>
            </td>
            <td rowspan="2" style="padding-left:15px">
                
            </td>
        </tr>
        <tr>
            <td>
                <span id="ctl00_AboutAuthorRptr_ctl00_AboutAuthor_memberLocation">Belgium <img src="/script/Geo/Images/BE.gif" alt="Belgium" width="16px" height="11px" /></span>
            </td>
        </tr>
        </table>
    </div>

    <div class="description">
        My name is Cedric and I live near Brussels, Belgium. Although I have an educational background in IT, I don't program for a living. My career has been more as an IT project leader and manager. But I want to stay up to date with the evolutions. Therefore, I dive into code after hours once in a while. Just to keep up with the programmers at work. You can read my blog at http://blog.baelemans.com<br />
<br />


        
    </div>

</div><br />
						
						

						<div class="clearfix"></div>

						<div style="padding-top:8px">
							
						</div>

					

				    
					</form>

				</div>

				
				<div class="bottom-promo"> 
				    <div class="msg-728x90" data-format="728x90" data-type="ad" data-publisher="lqm.codeproject.site" data-zone="Database/Database/SQL-Server"  data-country='US' data-loadOnView='true'  data-tags='C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,rating4.5,pos_bottom'><noscript><a href="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=728x90&c=910031"><img src="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=728x90&c=910031"  width="728px" height="90px" target="_blank"/></a></noscript></div>
				</div>
				
                
                

				
				

					<h2>Comments and Discussions</h2>
					<a class="float-left" name="_comments" id="_comments"> </a><div id="_MessageBoardctl00_MessageBoard" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(event, null)">
<table id="ForumTable" class="forum relaxed" cellpadding="0" cellspacing="0">
<tr>
<td class="header1"><div class="callout">
	<b>You must <a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL%3ffid%3d476191%26df%3d90%26mpp%3d25%26sort%3dPosition%26view%3dNormal%26spc%3dRelaxed%26prof%3dTrue%26select%3d4167414%26fr%3d1">Sign In</a> to use this message board.</b>
</div></td>
</tr><tr>
<td><table width="100%" border="0" cellpadding="3px" cellspacing="0">
<tr class="header1">
<td colspan="2" style="white-space:nowrap;"><div class="container">
<div class="float-right">
<form action="/Search.aspx?sbo=fm" method="get" class="searchbar">
<input type="hidden" name="fid" value="476191" /><input type="hidden" name="sbo" value="fm" /><input id="search_forumCtrl_ctl00_MessageBoard" type="search" class="search" autocomplete="off" name="qf" /><input type="image" src="https://codeproject.freetls.fastly.net/images/search.gif" />
</form>
</div>
</div></td>
</tr><tr class="header2">
<td></td><td style="width:100%;"><div style="text-align:right;">
<form action="/script/Forums/SetOptions.aspx?fid=476191&df=90&mpp=25&sort=Position&view=Normal&spc=Relaxed&prof=True&select=4167414&fr=1&floc=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL" method="get" style="margin:0;padding:0;">
<input type="hidden" name="fid" value="476191" /><input type="hidden" name="currentQS" value="fid=476191&df=90&mpp=25&sort=Position&view=Normal&spc=Relaxed&prof=True&select=4167414&fr=1&floc=%2fArticles%2f21068%2fAudit-Trail-Generator-for-Microsoft-SQL" /><input type="hidden" name="floc" value="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL" />Per page<select size="1" class="dropdown" name="mpp">
<option value="10">10</option><option selected value="25">25</option><option value="50">50</option>
</select>   <input type="submit" value="Go" name="SetOpt" class="button" />
</form>
</div></td>
</tr>
</table></td>
</tr><tr>
<td><a name="xx0xx"></a><table border="0" cellpadding="2px" cellspacing="0" width="100%">
<tr class="navbar">
<td></td><td style="text-align:right;white-space:nowrap;"><span class="nav-link disabled">First</span> <span class="nav-link disabled">Prev</span><a class="nav-link" name="Frm_HoverNL" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&view=Normal&spc=Relaxed&prof=True&select=4167414&fr=26#xx0xx">Next</a></td>
</tr>
</table></td>
</tr><tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%" class="fixed-layout blank-background">
<tr id="F5533967_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5533967xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5533967" parent="0" thread="5533967" href="/Messages/5533967/How-to-get-updated-fields-name.aspx">How to get updated fields name</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Member 13885865</span><span class="date">2-Jul-18  19:35</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5533967_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="13885865" msgid="5533967" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Hi. Thanks for this great SP. Now what can I add to update trigger to see the previous value or even just the updated fields name?<br />
All the best<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5533967" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5533967" data-ref="3_5533967" class="rating-label" name="RateItem_5533967"><span class="rating-result"><span></span></span></span><a href="/Messages/5533967/How-to-get-updated-fields-name.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5444465_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5444465xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5444465" parent="0" thread="5444465" href="/Messages/5444465/Oracle-version.aspx">Oracle version?</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Member 13464056</span><span class="date">13-Oct-17  11:11</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5444465_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="13464056" msgid="5444465" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Hello - is there a version of this for Oracle databases? Thank you!<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5444465" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5444465" data-ref="3_5444465" class="rating-label" name="RateItem_5444465"><span class="rating-result"><span></span></span></span><a href="/Messages/5444465/Oracle-version.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5395099_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5395099xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5395099" parent="0" thread="5395099" href="/Messages/5395099/how-to-save-original-row-in-shadow-table.aspx">how to save original row in shadow table</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">as45</span><span class="date">11-May-17  4:40</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5395099_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="8556475" msgid="5395099" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">the current procedure stores the entire row values in the shadow table after updating the row , so we can't know what is the value before doing the update!!<br />
any way to solve this?<br />
thank you<br />
<div class="signature">Abbas</div><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5395099" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5395099" data-ref="3_5395099" class="rating-label" name="RateItem_5395099"><span class="rating-result"><span></span></span></span><a href="/Messages/5395099/how-to-save-original-row-in-shadow-table.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5405608_h0" class="header hover-row">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="9px" class="indent"><a name="xx5405608xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_answer.gif" alt="Answer" title="Answer" /></td><td class="subject hover-container"><a class="message-link" name="5405608" parent="5395099" thread="5395099" href="/Messages/5405608/Re-how-to-save-original-row-in-shadow-table.aspx">Re: how to save original row in shadow table</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Member 11585200</span><span class="date">12-Jun-17  3:39</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5405608_h1" class="content selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:9px;"><span class="voteform vertical" ownerid="11585200" msgid="5405608" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2"><div class="parent"><a rel="nofollow" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(null,5395099);" href="/Messages/5395099/how-to-save-original-row-in-shadow-table.aspx"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/arrow-up24.png" title="Go to Parent"  style="width:16px;height:16px;border:0" /></a></div>If you are doing this on a new table, the row prior to the update is there. You just need to select on the PK ID you are interested in, then order by the audit Id.<br />
<br />
If you are doing this on an existing table, you will need to run a select on the table and insert into the audit table for the existing rows.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5395099" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5405608" data-ref="3_5405608" class="rating-label" name="RateItem_5405608"><span class="rating-result"><span></span></span></span><a href="/Messages/5405608/Re-how-to-save-original-row-in-shadow-table.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5413799_h0" class="header hover-row">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="18px" class="indent"><a name="xx5413799xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="5413799" parent="5405608" thread="5395099" href="/Messages/5413799/Re-how-to-save-original-row-in-shadow-table.aspx">Re: how to save original row in shadow table</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">as45</span><span class="date">8-Jul-17  21:40</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5413799_h1" class="content selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:18px;"><span class="voteform vertical" ownerid="8556475" msgid="5413799" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2"><div class="parent"><a rel="nofollow" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(null,5405608);" href="/Messages/5405608/Re-how-to-save-original-row-in-shadow-table.aspx"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/arrow-up24.png" title="Go to Parent"  style="width:16px;height:16px;border:0" /></a></div>many thanks with appreciations<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5395099" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5413799" data-ref="3_5413799" class="rating-label" name="RateItem_5413799"><span class="rating-result"><span></span></span></span><a href="/Messages/5413799/Re-how-to-save-original-row-in-shadow-table.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5329933_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5329933xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="5329933" parent="0" thread="5329933" href="/Messages/5329933/Ultimate-version.aspx">Ultimate version</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Patrick Lavoie</span><span class="date">22-Nov-16  5:27</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5329933_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="12850323" msgid="5329933" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I took this script and merged all the fixes in these comments and others found. Support audit in another database too if you want. Support updating the audit tables if new columns are added.<br />
<br />
<pre lang="SQL"><span class="code-keyword">SET</span> <span class="code-sdkkeyword">ANSI_NULLS</span> <span class="code-keyword">ON</span>
<span class="code-keyword">GO</span>

<span class="code-keyword">SET</span> <span class="code-sdkkeyword">QUOTED_IDENTIFIER</span> <span class="code-keyword">ON</span>
<span class="code-keyword">GO</span>

<span class="code-keyword">IF</span> <span class="code-sdkkeyword">OBJECT_ID</span>(<span class="code-string">'</span><span class="code-string">Utility.GenerateAuditTrail'</span>) <span class="code-keyword">IS</span> <span class="code-keyword">NULL</span>
<span class="code-keyword">BEGIN</span>
	<span class="code-keyword">PRINT</span>(<span class="code-string">'</span><span class="code-string">Create procedure Utility.GenerateAuditTrail'</span>);
	<span class="code-keyword">EXEC</span>(<span class="code-string">'</span><span class="code-string">CREATE PROCEDURE Utility.GenerateAuditTrail AS SET NOCOUNT ON;'</span>)
<span class="code-keyword">END</span>
<span class="code-keyword">ELSE</span>
	<span class="code-keyword">PRINT</span>(<span class="code-string">'</span><span class="code-string">Alter procedure Utility.GenerateAuditTrail'</span>);
<span class="code-keyword">GO</span>
 
<span class="code-keyword">ALTER</span> <span class="code-keyword">PROCEDURE</span> Utility.GenerateAuditTrail
     <span class="code-sdkkeyword">@Owner</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>)
    ,<span class="code-sdkkeyword">@TableName</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>)
    ,<span class="code-sdkkeyword">@AuditNameExtension</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>) = <span class="code-string">'</span><span class="code-string">_Audit'</span>
    ,<span class="code-sdkkeyword">@DropAuditTable</span> <span class="code-keyword">BIT</span> = <span class="code-digit">0</span>
    ,<span class="code-sdkkeyword">@AuditDatabaseName</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>) = <span class="code-keyword">NULL</span>
<span class="code-keyword">AS</span>
<span class="code-keyword">BEGIN</span>

    <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@AuditDatabaseName</span> <span class="code-keyword">IS</span> <span class="code-keyword">NULL</span>
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@AuditDatabaseName</span> = (<span class="code-keyword">SELECT</span> <span class="code-sdkkeyword">DB_NAME</span>())
    <span class="code-keyword">ELSE</span>
	   <span class="code-keyword">IF</span> <span class="code-keyword">NOT</span> <span class="code-keyword">EXISTS</span>(<span class="code-keyword">SELECT</span> name <span class="code-keyword">FROM</span> sys.databases <span class="code-keyword">WHERE</span> name = <span class="code-sdkkeyword">@AuditDatabaseName</span>)
	   <span class="code-keyword">BEGIN</span>
		  <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">ERROR: Database'''</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">'' does not exist'</span>;
		  <span class="code-keyword">RETURN</span>;  
	   <span class="code-keyword">END</span>

    <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Current database : '</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span>;

    <span class="code-comment">--</span><span class="code-comment"> Check if table exists</span>
    <span class="code-keyword">IF</span> <span class="code-keyword">NOT</span> <span class="code-keyword">EXISTS</span>(<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>)
    <span class="code-keyword">BEGIN</span>
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">ERROR: Table does not exist'</span>;
	   <span class="code-keyword">RETURN</span>;
    <span class="code-keyword">END</span>
    
    <span class="code-comment">--</span><span class="code-comment"> Check @AuditNameExtension</span>
    <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@AuditNameExtension</span> <span class="code-keyword">IS</span> <span class="code-keyword">NULL</span>
    <span class="code-keyword">BEGIN</span>
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">ERROR: @AuditNameExtension cannot be null'</span>;
	   <span class="code-keyword">RETURN</span>;
    <span class="code-keyword">END</span>

    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span> <span class="code-keyword">NVARCHAR</span>(MAX);
    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span> = <span class="code-string">'</span><span class="code-string">EXEC '</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">..sp_executesql N''
    IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = '''''</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">'''')
	   EXECUTE(N''''CREATE SCHEMA '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">;'''');'''</span>;
    <span class="code-keyword">EXEC</span> sp_executesql <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span>;

    <span class="code-comment">--</span><span class="code-comment"> Drop audit table if it exists and drop should be forced</span>
    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span> = <span class="code-string">'</span><span class="code-string">
    IF (EXISTS(SELECT * FROM '</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">.sys.sysobjects WHERE id = OBJECT_ID(N''['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1) AND '</span> + CAST(<span class="code-sdkkeyword">@DropAuditTable</span> <span class="code-keyword">AS</span> <span class="code-keyword">CHAR</span>(<span class="code-digit">1</span>)) + <span class="code-string">'</span><span class="code-string"> = 1)
    BEGIN
	   PRINT ''Dropping audit table ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">];
	   DROP TABLE ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'';
    END'</span>;
    <span class="code-keyword">EXEC</span> sp_executesql <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span>;
    
    <span class="code-comment">--</span><span class="code-comment"> Declare cursor to loop over columns</span>
    <span class="code-keyword">DECLARE</span> TableColumns <span class="code-keyword">CURSOR</span> Read_Only
    <span class="code-keyword">FOR</span> <span class="code-keyword">SELECT</span> b.name, c.name <span class="code-keyword">as</span> TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
	   <span class="code-keyword">FROM</span> 
		  sys.sysobjects a 
		  <span class="code-keyword">INNER</span> <span class="code-keyword">JOIN</span> sys.syscolumns b <span class="code-keyword">on</span> a.id = b.id 
		  <span class="code-keyword">INNER</span> <span class="code-keyword">JOIN</span> sys.systypes c <span class="code-keyword">on</span> b.xusertype  = c.xusertype  <span class="code-keyword">and</span> c.name <> <span class="code-string">'</span><span class="code-string">sysname'</span> 
	   <span class="code-keyword">WHERE</span> 
		  a.id = <span class="code-sdkkeyword">OBJECT_ID</span>(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>) 
		  <span class="code-keyword">AND</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(a.id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span> 
	   <span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span> 
		  b.colId;
    
    <span class="code-keyword">OPEN</span> TableColumns;
    
    <span class="code-comment">--</span><span class="code-comment"> Declare temp variable to fetch records into</span>
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>);
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">128</span>);
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">SMALLINT</span>;
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnNullable</span> <span class="code-keyword">INT</span>;
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnCollation</span> SYSNAME;
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnPrecision</span> <span class="code-keyword">TINYINT</span>;
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnScale</span> <span class="code-keyword">TINYINT</span>;
    
    <span class="code-comment">--</span><span class="code-comment"> Declare variable to build statements</span>
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@CreateStatement</span> <span class="code-keyword">VARCHAR</span>(MAX);
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ListOfFields</span> <span class="code-keyword">VARCHAR</span>(MAX);
    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-string">'</span><span class="code-string">'</span>;    
    
    <span class="code-comment">--</span><span class="code-comment"> Check if audit table exists</span>
    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@IsAuditTableExistsInAuditDatabase</span> <span class="code-keyword">BIT</span>;
    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span> = <span class="code-string">'</span><span class="code-string">EXEC '</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">..sp_executesql N'''</span> + <span class="code-string">'</span><span class="code-string">
    IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id = OBJECT_ID(N''''['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'''') AND OBJECTPROPERTY(id, N''''IsUserTable'''') = 1) SET @IsAuditTableExistsInAuditDatabase = 1; ELSE SET @IsAuditTableExistsInAuditDatabase = 0;'', N''@IsAuditTableExistsInAuditDatabase BIT OUTPUT'', @IsAuditTableExistsInAuditDatabase OUTPUT'</span>
    <span class="code-keyword">EXEC</span> sp_executesql <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span>, N<span class="code-string">'</span><span class="code-string">@IsAuditTableExistsInAuditDatabase BIT OUTPUT'</span>, <span class="code-sdkkeyword">@IsAuditTableExistsInAuditDatabase</span> <span class="code-keyword">OUTPUT</span>;
    
    <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@IsAuditTableExistsInAuditDatabase</span> = <span class="code-digit">1</span>
    <span class="code-keyword">BEGIN</span>
	   <span class="code-comment">--</span><span class="code-comment"> AuditTable exists, update needed</span>
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Table already exists. Will update table schema with new fields.'</span>;
	   
	   <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@NewFields</span> <span class="code-keyword">VARCHAR</span>(MAX) = <span class="code-string">'</span><span class="code-string">'</span>,
			 <span class="code-sdkkeyword">@ExistingFields</span> <span class="code-keyword">VARCHAR</span>(MAX)
    
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span> = <span class="code-string">'</span><span class="code-string">EXEC '</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">..sp_executesql N'''</span> + <span class="code-string">'</span><span class="code-string">
	   DECLARE @ExcludeColumnNames VARCHAR(MAX) = '''',AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp,AuditTransactionId,''''
	   SELECT 
		  @ExistingFields = COALESCE(@ExistingFields, '''''''') + '''','''' + sc.name + '''',''''
	   FROM
		  sysobjects so 
		  INNER JOIN syscolumns sc on so.id = sc.id 
	   WHERE 
		  so.id = OBJECT_ID(N''''['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'''') 
		  AND OBJECTPROPERTY(so.id, N''''IsUserTable'''') = 1 
		  AND CHARINDEX('''','''' + sc.name + '''','''', @ExcludeColumnNames) = 0
	   ORDER BY 
		  sc.colId'', N''@ExistingFields VARCHAR(MAX) OUTPUT'', @ExistingFields OUTPUT;'</span>;
	   <span class="code-keyword">EXEC</span> sp_executesql <span class="code-sdkkeyword">@ExecuteInAuditDatabase</span>, N<span class="code-string">'</span><span class="code-string">@ExistingFields VARCHAR(MAX) OUTPUT'</span>, <span class="code-sdkkeyword">@ExistingFields</span> <span class="code-keyword">OUTPUT</span>;
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Existing fields : '</span> + ISNULL(<span class="code-sdkkeyword">@ExistingFields</span>, <span class="code-string">'</span><span class="code-string">'</span>);

	   <span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
	   <span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>;
    	
	   <span class="code-keyword">WHILE</span> <span class="code-systemcall">@@FETCH_STATUS</span> = <span class="code-digit">0</span>
	   <span class="code-keyword">BEGIN</span>
    		  <span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">text'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">ntext'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">image'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">timestamp'</span>)
    		  <span class="code-keyword">BEGIN</span>
    			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">,['</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">]'</span>;
    		  <span class="code-keyword">END</span>

		  <span class="code-keyword">IF</span> (CHARINDEX(<span class="code-string">'</span><span class="code-string">,'</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">,'</span>, <span class="code-sdkkeyword">@ExistingFields</span>) = <span class="code-digit">0</span>)
		  <span class="code-keyword">BEGIN</span>
			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">,['</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">] ['</span> + <span class="code-sdkkeyword">@ColumnType</span> + <span class="code-string">'</span><span class="code-string">] '</span>;
    			
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">binary'</span>, <span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">varbinary'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>)
			 <span class="code-keyword">BEGIN</span>
				<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
				    <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">(MAX) '</span>;
				<span class="code-keyword">ELSE</span>
				    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
			 <span class="code-keyword">END</span>
 
			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>)
			 <span class="code-keyword">BEGIN</span>
				<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
				    <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">(MAX) '</span>;
				<span class="code-keyword">ELSE</span>
				    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast((<span class="code-sdkkeyword">@ColumnLength</span> / <span class="code-digit">2</span> ) <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
			 <span class="code-keyword">END</span>
    	
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">decimal'</span>, <span class="code-string">'</span><span class="code-string">numeric'</span>)
    				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnPrecision</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">,'</span> + cast(<span class="code-sdkkeyword">@ColumnScale</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
    	
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>, <span class="code-string">'</span><span class="code-string">text'</span>, <span class="code-string">'</span><span class="code-string">ntext'</span>)
    				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">COLLATE '</span> + <span class="code-sdkkeyword">@ColumnCollation</span> + <span class="code-string">'</span><span class="code-string"> '</span>;
    	
			 <span class="code-comment">--</span><span class="code-comment"> Why put not nullable? Blocks some changes like new columns</span>
    			 <span class="code-comment">--</span><span class="code-comment">IF @ColumnNullable = 0</span>
    				<span class="code-comment">--</span><span class="code-comment">SET @NewFields = @NewFields + 'NOT '	;	</span>
    	
    			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@NewFields</span> = <span class="code-sdkkeyword">@NewFields</span> + <span class="code-string">'</span><span class="code-string">NULL'</span>; 
		  <span class="code-keyword">END</span>
    
    		  <span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
    		  <span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>;
    
	   <span class="code-keyword">END</span>

	   <span class="code-keyword">IF</span> LEN(<span class="code-sdkkeyword">@NewFields</span>) > <span class="code-digit">0</span>
	   <span class="code-keyword">BEGIN</span>
		  <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">ALTER TABLE ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">] ADD '</span> + SUBSTRING(<span class="code-sdkkeyword">@NewFields</span>, <span class="code-digit">2</span>, LEN(<span class="code-sdkkeyword">@NewFields</span>));

		  <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Adding new Fields to audit table ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'</span>
		  <span class="code-keyword">PRINT</span> <span class="code-sdkkeyword">@CreateStatement</span>
		  <span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>)
	   <span class="code-keyword">END</span>
	   <span class="code-keyword">ELSE</span>
		  <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">No new fields to add to the audit table'</span>
    <span class="code-keyword">END</span>
    <span class="code-keyword">ELSE</span>
    <span class="code-keyword">BEGIN</span>
	   <span class="code-comment">--</span><span class="code-comment"> AuditTable does not exist, create new</span>
    
	   <span class="code-comment">--</span><span class="code-comment"> Start of create table</span>
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">CREATE TABLE ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">] ('</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">[AuditId] [BIGINT] IDENTITY (1, 1) NOT NULL'</span>;
	   
	   <span class="code-comment">--</span><span class="code-comment"> Add audit trail columns</span>
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,[AuditDate] [DATETIME] NOT NULL'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL'</span> ;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,[AuditTransactionId] [BIGINT] NOT NULL'</span>;
    
	   <span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
	   <span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>;
    	
	   <span class="code-keyword">WHILE</span> <span class="code-systemcall">@@FETCH_STATUS</span> = <span class="code-digit">0</span>
	   <span class="code-keyword">BEGIN</span>
    		  <span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">text'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">ntext'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">image'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">timestamp'</span>)
    		  <span class="code-keyword">BEGIN</span>
    			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">,['</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">]'</span>;
    	
    			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,['</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">] ['</span> + <span class="code-sdkkeyword">@ColumnType</span> + <span class="code-string">'</span><span class="code-string">] '</span>;
    			
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">binary'</span>, <span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">varbinary'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>)
			 <span class="code-keyword">BEGIN</span>
				<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
				    <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">(MAX) '</span>;
				<span class="code-keyword">ELSE</span>
				    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
			 <span class="code-keyword">END</span>
 
			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>)
			 <span class="code-keyword">BEGIN</span>
				<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
				    <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">(MAX) '</span>;
				<span class="code-keyword">ELSE</span>
				    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast((<span class="code-sdkkeyword">@ColumnLength</span> / <span class="code-digit">2</span> ) <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
			 <span class="code-keyword">END</span>
    	
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">decimal'</span>, <span class="code-string">'</span><span class="code-string">numeric'</span>)
    				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnPrecision</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">,'</span> + cast(<span class="code-sdkkeyword">@ColumnScale</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>;
    	
    			 <span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>, <span class="code-string">'</span><span class="code-string">text'</span>, <span class="code-string">'</span><span class="code-string">ntext'</span>)
    				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">COLLATE '</span> + <span class="code-sdkkeyword">@ColumnCollation</span> + <span class="code-string">'</span><span class="code-string"> '</span>;
    	
			 <span class="code-comment">--</span><span class="code-comment"> Why put not nullable? Blocks some changes like new columns</span>
    			 <span class="code-comment">--</span><span class="code-comment">IF @ColumnNullable = 0</span>
    				<span class="code-comment">--</span><span class="code-comment">SET @CreateStatement = @CreateStatement + 'NOT '	;	</span>
    	
    			 <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">NULL'</span>; 	
    		  <span class="code-keyword">END</span>
    
    		  <span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
    		  <span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>;
	   <span class="code-keyword">END</span>
    
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">)'</span>;
	    	
	   <span class="code-comment">--</span><span class="code-comment"> Create audit table</span>
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Creating audit table ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">]'</span>;
	   <span class="code-keyword">PRINT</span> <span class="code-sdkkeyword">@CreateStatement</span>;
	   <span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>);
    
	   <span class="code-comment">--</span><span class="code-comment"> Set primary key and default values</span>
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">ALTER TABLE ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">] ADD '</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">_AuditDate] DEFAULT (GETDATE()) FOR [AuditDate]'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">_AuditUser] DEFAULT (SUSER_SNAME()) FOR [AuditUser]'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,CONSTRAINT [PK_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">] PRIMARY KEY  CLUSTERED ([AuditId])  ON [PRIMARY]'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">_AuditApp]  DEFAULT (''App=('' + RTRIM(ISNULL(APP_NAME(),'''')) + '') '') for [AuditApp]'</span>;
	   <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">,CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">_AuditTransactionId] DEFAULT (0) FOR [AuditTransactionId]'</span>;
    
	   <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Setting primary key and default values'</span>
	   <span class="code-keyword">PRINT</span> <span class="code-sdkkeyword">@CreateStatement</span>;
	   <span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>);
    
    <span class="code-keyword">END</span>
    
    <span class="code-keyword">CLOSE</span> TableColumns;
    <span class="code-keyword">DEALLOCATE</span> TableColumns;
    
    <span class="code-comment">/*</span><span class="code-comment"> Drop Triggers, if they exist */</span>
    <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Dropping triggers'</span>
    <span class="code-keyword">IF</span> <span class="code-keyword">EXISTS</span>(<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
	   <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">DROP TRIGGER ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert]'</span>);
    
    <span class="code-keyword">IF</span> <span class="code-keyword">EXISTS</span>(<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
	   <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">DROP TRIGGER ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update]'</span>);
    
    <span class="code-keyword">IF</span> <span class="code-keyword">EXISTS</span>(<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
	   <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">DROP TRIGGER ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete]'</span>);
    
    <span class="code-comment">/*</span><span class="code-comment"> Create triggers */</span>
    <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Creating triggers'</span>;
    <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR INSERT AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">](AuditAction/*, AuditTransactionId*/'</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">) SELECT ''I''/*, @TransactionId*/'</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string"> FROM Inserted; END'</span>);
    <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR UPDATE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">](AuditAction/*, AuditTransactionId*/'</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">) SELECT ''U''/*, @TransactionId*/'</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string"> FROM Inserted; END'</span>);
    <span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER TR_Audit_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR DELETE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO ['</span> + <span class="code-sdkkeyword">@AuditDatabaseName</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtension</span> + <span class="code-string">'</span><span class="code-string">](AuditAction/*, AuditTransactionId*/'</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">) SELECT ''D''/*, @TransactionId*/'</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string"> FROM Deleted; END'</span>);

<span class="code-keyword">END</span>
<span class="code-keyword">GO</span></pre><br />
<br />
<br />
Here a script to create/update all audit tables :<br />
<br />
<pre lang="SQL"><span class="code-keyword">SET</span> <span class="code-sdkkeyword">ANSI_NULLS</span> <span class="code-keyword">ON</span>
<span class="code-keyword">GO</span>

<span class="code-keyword">SET</span> <span class="code-sdkkeyword">QUOTED_IDENTIFIER</span> <span class="code-keyword">ON</span>
<span class="code-keyword">GO</span>

<span class="code-keyword">IF</span> <span class="code-sdkkeyword">OBJECT_ID</span>(<span class="code-string">'</span><span class="code-string">Utility.UpdateAllAuditTrails'</span>) <span class="code-keyword">IS</span> <span class="code-keyword">NULL</span>
<span class="code-keyword">BEGIN</span>
	<span class="code-keyword">PRINT</span>(<span class="code-string">'</span><span class="code-string">Create procedure Utility.UpdateAllAuditTrails'</span>);
	<span class="code-keyword">EXEC</span>(<span class="code-string">'</span><span class="code-string">CREATE PROCEDURE Utility.UpdateAllAuditTrails AS SET NOCOUNT ON;'</span>)
<span class="code-keyword">END</span>
<span class="code-keyword">ELSE</span>
	<span class="code-keyword">PRINT</span>(<span class="code-string">'</span><span class="code-string">Alter procedure Utility.UpdateAllAuditTrails'</span>);
<span class="code-keyword">GO</span>
 
<span class="code-keyword">ALTER</span> <span class="code-keyword">PROCEDURE</span> Utility.UpdateAllAuditTrails
     <span class="code-sdkkeyword">@RecreateAuditTables</span> <span class="code-keyword">BIT</span> = <span class="code-digit">0</span>
<span class="code-keyword">AS</span>
<span class="code-keyword">BEGIN</span>

    <span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@output</span> <span class="code-keyword">NVARCHAR</span>(MAX) = N<span class="code-string">'</span><span class="code-string">'</span>;

    ;<span class="code-keyword">WITH</span>
    Tables <span class="code-keyword">AS</span> 
    (
	   <span class="code-keyword">SELECT</span>
		   s.name <span class="code-keyword">AS</span> [<span class="code-keyword">Schema</span>]
		  ,t.name <span class="code-keyword">AS</span> [<span class="code-keyword">Table</span>]
	   <span class="code-keyword">FROM</span>
		  sys.tables t
		  <span class="code-keyword">INNER</span> <span class="code-keyword">JOIN</span> sys.schemas s <span class="code-keyword">ON</span> t.schema_id = s.schema_id
	   <span class="code-keyword">WHERE</span>
		  CHARINDEX(<span class="code-string">'</span><span class="code-string">,'</span> + s.name + <span class="code-string">'</span><span class="code-string">,'</span>, <span class="code-string">'</span><span class="code-string">,IgnoreSchema1,
								   ,IgnoreSchema2,
								   ,IgnoreSchema3,
								   '</span>) = <span class="code-digit">0</span>
		  <span class="code-keyword">AND</span> CHARINDEX(<span class="code-string">'</span><span class="code-string">,'</span> + t.name + <span class="code-string">'</span><span class="code-string">,'</span>, <span class="code-string">'</span><span class="code-string">,IgnoreTable1,
									  ,IgnoreTable2,									  
									  ,IgnoreTable3,
									  '</span>) = <span class="code-digit">0</span>
		  <span class="code-keyword">AND</span> t.name <span class="code-keyword">NOT</span> <span class="code-keyword">LIKE</span> <span class="code-string">'</span><span class="code-string">%Logs'</span>
		  <span class="code-keyword">AND</span> t.name <span class="code-keyword">NOT</span> <span class="code-keyword">LIKE</span> <span class="code-string">'</span><span class="code-string">%_Audit'</span>
	   <span class="code-comment">--</span><span class="code-comment">ORDER BY</span>
		  <span class="code-comment">--</span><span class="code-comment">s.name, t.name</span>
    )

    <span class="code-keyword">SELECT</span> 
	   <span class="code-sdkkeyword">@output</span> += <span class="code-keyword">CHAR</span>(<span class="code-digit">13</span>) + <span class="code-keyword">CHAR</span>(<span class="code-digit">10</span>) + <span class="code-string">'</span><span class="code-string">EXEC Utility.GenerateAuditTrail '''</span> + t.[<span class="code-keyword">Schema</span>] + <span class="code-string">'</span><span class="code-string">'', '''</span> + t.[<span class="code-keyword">Table</span>] + <span class="code-string">'</span><span class="code-string">'', @DropAuditTable='</span> + CAST(<span class="code-sdkkeyword">@RecreateAuditTables</span> <span class="code-keyword">AS</span> <span class="code-keyword">CHAR</span>(<span class="code-digit">1</span>)) + <span class="code-string">'</span><span class="code-string">, @AuditDatabaseName='''</span> + (<span class="code-keyword">SELECT</span> <span class="code-sdkkeyword">DB_NAME</span>() + <span class="code-string">'</span><span class="code-string">_Audit'</span>) + <span class="code-string">'</span><span class="code-string">'';'</span>
    <span class="code-keyword">FROM</span>
	   Tables t
    <span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span>  t.[<span class="code-keyword">Schema</span>]
		   ,t.[<span class="code-keyword">Table</span>]

    <span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Output : '</span> + <span class="code-sdkkeyword">@output</span>;
    <span class="code-keyword">EXEC</span> sp_executesql <span class="code-sdkkeyword">@output</span>;

<span class="code-keyword">END</span>
<span class="code-keyword">GO</span></pre><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5329933" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5329933" data-ref="3_5329933" class="rating-label" name="RateItem_5329933"><span class="rating-result"><span></span></span></span><a href="/Messages/5329933/Ultimate-version.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5300567_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5300567xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_bug.gif" alt="Bug" title="Bug" /></td><td class="subject hover-container"><a class="message-link" name="5300567" parent="0" thread="5300567" href="/Messages/5300567/Dont-use-GetDate-your-audit-trail-will-be-dangerou.aspx">Don't use GetDate() - your audit trail will be dangerously wrong</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">pt1401</span><span class="date">15-Sep-16  12:35</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5300567_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="6999635" msgid="5300567" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Using GetDate() to determine the order of events is dangerous. All datetimes stored in a database should be stored in UTC and converted to local time at the client - this is databases 101.<br />
<br />
Consider what happens at a daylight-saving switch and the clocks go back...<br />
Event 1: 02:59:50<br />
Event 2: 02:59:59<br />
Event 3: 02:00:01<br />
<br />
You've just screwed your audit records.<br />
<br />
Always store time in UTC - GetUtcDate() instead on GetDate()<br />
Convert to local time on display (local being defined by the user looking at the data)<br />
<br />
Also consider using DATETIME2 instead of DATETIME.<br /><br /><br /><div class="modified">modified  15-Sep-16 17:50pm.</div><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5300567" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5300567" data-ref="3_5300567" class="rating-label" name="RateItem_5300567"><span class="rating-result"><span></span></span></span><a href="/Messages/5300567/Dont-use-GetDate-your-audit-trail-will-be-dangerou.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5300474_h0" class="header hover-row root">
<td class="subject-line normal vote-hi " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5300474xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5300474" parent="0" thread="5300474" href="/Messages/5300474/Suggestion-for-changes-in-GenerateAudittrail-Store.aspx">Suggestion for changes in 'GenerateAudittrail' Stored Procedure</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">VISHAL110988</span><span class="date">15-Sep-16  9:03</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5300474_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="8397557" msgid="5300474" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I received an error on executing this Stored Procedure<br />
"Invalid Column name....."<br />
<br />
After some research i found that variable "@ListOfFields" was causing the issue<br />
Since i had more than 100 columns in my source table what caused the @ListOfFields varchar(2000) to exceed more than 2000 character so list of column names got truncated.<br />
<br />
So Update the Variable @ListOfFields to varchar(max).<br />
<br />
And Great Job. This made my life easy to maintain the log....<img src="https://codeproject.global.ssl.fastly.net/script/Forums/Images/thumbs_up.gif" align="top" alt="Thumbs Up | :thumbsup:" /><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5300474" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5300474" data-ref="3_5300474" class="rating-label" name="RateItem_5300474"><span class="rating-result"><span></span></span></span><a href="/Messages/5300474/Suggestion-for-changes-in-GenerateAudittrail-Store.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5292725_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5292725xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_idea.gif" alt="Suggestion" title="Suggestion" /></td><td class="subject hover-container"><a class="message-link" name="5292725" parent="0" thread="5292725" href="/Messages/5292725/Additional-fix-for-geography-hierarchyid-geometry.aspx">Additional fix for geography/hierarchyid/geometry types</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">midix</span><span class="date">29-Aug-16  0:15</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5292725_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="2435692" msgid="5292725" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">To avoid the script complaining about multiple columns (the reason of the problem described here:<br />
<br />
<a href="http://stackoverflow.com/questions/28477177/sql-server-geography-type-creates-multiple-types-for-the-same-variable">sqlgeography - SQL Server Geography type creates multiple types for the same variable - Stack Overflow</a>[<a href="http://stackoverflow.com/questions/28477177/sql-server-geography-type-creates-multiple-types-for-the-same-variable" target="_blank" title="New Window">^</a>] ), you can fix the systypes JOIN as follows: <br />
<br />
<pre lang="SQL"><span class="code-keyword">INNER</span> <span class="code-keyword">JOIN</span> systypes c <span class="code-keyword">ON</span> b.xtype = c.xtype <span class="code-keyword">AND</span> b.xusertype = c.xusertype <span class="code-keyword">AND</span> c.name <> <span class="code-string">'</span><span class="code-string">sysname'</span></pre><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5292725" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5292725" data-ref="3_5292725" class="rating-label" name="RateItem_5292725"><span class="rating-result"><span></span></span></span><a href="/Messages/5292725/Additional-fix-for-geography-hierarchyid-geometry.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5249008_h0" class="header hover-row root">
<td class="subject-line normal vote-hi " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5249008xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5249008" parent="0" thread="5249008" href="/Messages/5249008/Great-Job-One-modification-to-update-table-schema.aspx">Great Job! One modification to update table schema if source table change</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">miguelgalindo</span><span class="date">18-May-16  6:09</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5249008_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="5250854" msgid="5249008" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Great Job! I added one modification to fit into my project, this change is handling the case the source table change (like when upgrading software) it is updating the schema instead of deleting the table. This way we keep the records, if any field no longer exists it remains in the audit table but is excluded from the listOfFields so triggers will ignore it.<br />
<br />
<pre lang="SQL"><span class="code-keyword">IF</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>)
				<span class="code-keyword">AND</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>)
	<span class="code-keyword">BEGIN</span>
		<span class="code-comment">--</span><span class="code-comment"> AuditTable exists, update needed</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Table already exists. Will update table schema with new fields.'</span>

		<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ExcludeColumnNames</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">2000</span>), 
				<span class="code-sdkkeyword">@NewFields</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">2000</span>) = <span class="code-string">'</span><span class="code-string">'</span>,
				<span class="code-sdkkeyword">@ExistingFields</span> <span class="code-keyword">VARCHAR</span>(<span class="code-digit">2000</span>)

		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ExcludeColumnNames</span> = <span class="code-string">'</span><span class="code-string">AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp'</span>

		<span class="code-keyword">SELECT</span> <span class="code-sdkkeyword">@ExistingFields</span> = <span class="code-keyword">COALESCE</span>(<span class="code-sdkkeyword">@ExistingFields</span> + <span class="code-string">'</span><span class="code-string">,'</span>, <span class="code-string">'</span><span class="code-string">'</span>) + sc.name
		<span class="code-keyword">FROM</span> sysobjects so 
			<span class="code-keyword">INNER</span> <span class="code-keyword">JOIN</span> syscolumns sc <span class="code-keyword">on</span> so.id = sc.id 
		<span class="code-keyword">WHERE</span> so.id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>) 
			<span class="code-keyword">AND</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(so.id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span> 
			<span class="code-keyword">AND</span> CHARINDEX(sc.name, <span class="code-sdkkeyword">@ExcludeColumnNames</span>) = <span class="code-digit">0</span>
		<span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span> sc.colId

		<span class="code-comment">--</span><span class="code-comment"> Start ALTER table in case there are new fields</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">ALTER TABLE ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">] ADD '</span>

		<span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
		<span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>
		
		<span class="code-keyword">WHILE</span> <span class="code-systemcall">@@FETCH_STATUS</span> = <span class="code-digit">0</span>
		<span class="code-keyword">BEGIN</span>
                        <span class="code-comment">--</span><span class="code-comment">Only simple types are allowed</span>
			<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">text'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">ntext'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">image'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">timestamp'</span>)
			<span class="code-keyword">BEGIN</span>
				<span class="code-comment">--</span><span class="code-comment"> Get original table fields, used to create triggers later</span>
				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-sdkkeyword">@SourceTableAlias</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@ColumnName</span> + <span class="code-string">'</span><span class="code-string">,'</span>
                     
                               ... More statements here

                       <span class="code-keyword">END</span>
                <span class="code-keyword">END</span>

               <span class="code-comment">--</span><span class="code-comment">take out the last ','</span>
               <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-keyword">LEFT</span>(<span class="code-sdkkeyword">@CreateStatement</span>, DATALENGTH(<span class="code-sdkkeyword">@CreateStatement</span>) -1) 
		
		<span class="code-comment">--</span><span class="code-comment"> Execute Alter audit table if there are new fields</span>
		<span class="code-keyword">IF</span>(LEN(<span class="code-sdkkeyword">@NewFields</span>) > <span class="code-digit">0</span>)
		<span class="code-keyword">BEGIN</span>
			<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Adding new Fields to audit table ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>
			<span class="code-keyword">PRINT</span> <span class="code-sdkkeyword">@CreateStatement</span>
			<span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>)
		<span class="code-keyword">END</span>
	<span class="code-keyword">END</span></pre><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5249008" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5249008" data-ref="3_5249008" class="rating-label" name="RateItem_5249008"><span class="rating-result"><span></span></span></span><a href="/Messages/5249008/Great-Job-One-modification-to-update-table-schema.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5326822_h0" class="header hover-row">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="9px" class="indent"><a name="xx5326822xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_answer.gif" alt="Answer" title="Answer" /></td><td class="subject hover-container"><a class="message-link" name="5326822" parent="5249008" thread="5249008" href="/Messages/5326822/Re-Great-Job-One-modification-to-update-table-sche.aspx">Re: Great Job! One modification to update table schema if source table change</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Patrick Lavoie</span><span class="date">14-Nov-16  12:16</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5326822_h1" class="content selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:9px;"><span class="voteform vertical" ownerid="12850323" msgid="5326822" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2"><div class="parent"><a rel="nofollow" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(null,5249008);" href="/Messages/5249008/Great-Job-One-modification-to-update-table-schema.aspx"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/arrow-up24.png" title="Go to Parent"  style="width:16px;height:16px;border:0" /></a></div>Do you have the full script? Because the snippet is incomplete, missing variables, missing parts. I think you pasted a wrong/old copy of the script you had...<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5249008" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5326822" data-ref="3_5326822" class="rating-label" name="RateItem_5326822"><span class="rating-result"><span></span></span></span><a href="/Messages/5326822/Re-Great-Job-One-modification-to-update-table-sche.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5142167_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5142167xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5142167" parent="0" thread="5142167" href="/Messages/5142167/Get-hostname-of-client.aspx">Get hostname of client</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Vandrer</span><span class="date">12-Oct-15  5:49</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5142167_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="10211005" msgid="5142167" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Instead of suser_sname() I'd like to capture the hostname of the client. However, Host_Name() only seems to capture the hostname of the server itself.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5142167" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5142167" data-ref="3_5142167" class="rating-label" name="RateItem_5142167"><span class="rating-result"><span></span></span></span><a href="/Messages/5142167/Get-hostname-of-client.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5100882_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx5100882xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="5100882" parent="0" thread="5100882" href="/Messages/5100882/Parameter-exceeds-the-maximum-allowed.aspx">Parameter exceeds the maximum allowed?</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Member 11872276</span><span class="date">31-Jul-15  1:44</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5100882_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="11872276" msgid="5100882" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I receive the follow error when attempting to create a _shadow table on certain tables.  From trying to troubleshoot I think this is because of the size of the generated SP?  Any help/tip appreciated.  If there's specific info I can post to help please let me know.<br />
<br />
<pre>Msg 2717, Level 16, State 2, Line 1
The size (8000) given to the parameter 'Summary' exceeds the maximum allowed (4000).</pre><br />
<br />
Best Regards,<br />
<br />
Chris<br />
<br />
Update: I changed the EXEC statements to PRINT, manually changed the fields where the auto generated fields were to long and created _shadow table manually.<br /><br /><div class="modified">modified  1-Aug-15 17:36pm.</div><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=5100882" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5100882" data-ref="3_5100882" class="rating-label" name="RateItem_5100882"><span class="rating-result"><span></span></span></span><a href="/Messages/5100882/Parameter-exceeds-the-maximum-allowed.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4950960_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4950960xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="4950960" parent="0" thread="4950960" href="/Messages/4950960/Error-Message-The-row-value-s-updated-or-deleted-e.aspx">Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Member 1746845</span><span class="date">25-Nov-14  5:20</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4950960_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="1746845" msgid="4950960" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">---------------------------<br />
Microsoft SQL Server Management Studio<br />
---------------------------<br />
No rows were deleted.<br />
<br />
A problem occurred attempting to delete row 1.<br />
Error Source: Microsoft.SqlServer.Management.DataTools.<br />
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).<br />
<br />
Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).<br />
---------------------------<br />
OK   Help   <br />
---------------------------<br />
<br />
<br />
Fix For the above Error:<br />
=======================<br />
<br />
addition of SET NOCOUNT ON; for each triggers fixes.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4950960" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4950960" data-ref="3_4950960" class="rating-label" name="RateItem_4950960"><span class="rating-result"><span></span></span></span><a href="/Messages/4950960/Error-Message-The-row-value-s-updated-or-deleted-e.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5022868_h0" class="header hover-row">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="9px" class="indent"><a name="xx5022868xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_answer.gif" alt="Answer" title="Answer" /></td><td class="subject hover-container"><a class="message-link" name="5022868" parent="4950960" thread="4950960" href="/Messages/5022868/Re-Error-Message-The-row-value-s-updated-or-delete.aspx">Re: Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Mohann2015</span><span class="date">17-Mar-15  22:32</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5022868_h1" class="content selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:9px;"><span class="voteform vertical" ownerid="6009034" msgid="5022868" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2"><div class="parent"><a rel="nofollow" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(null,4950960);" href="/Messages/4950960/Error-Message-The-row-value-s-updated-or-deleted-e.aspx"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/arrow-up24.png" title="Go to Parent"  style="width:16px;height:16px;border:0" /></a></div>can you tell me where to insert the set nocount on; in the code? thanks<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4950960" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5022868" data-ref="3_5022868" class="rating-label" name="RateItem_5022868"><span class="rating-result"><span></span></span></span><a href="/Messages/5022868/Re-Error-Message-The-row-value-s-updated-or-delete.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4756811_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4756811xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="4756811" parent="0" thread="4756811" href="/Messages/4756811/Excellent-Code-Sample.aspx">Excellent Code Sample</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">lylehardin</span><span class="date">11-Feb-14  9:44</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4756811_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="2152013" msgid="4756811" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Thank you very much for taking the time to code and post this example.  I am using it now to track down a problem of table rows inexplicable disappearing.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4756811" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4756811" data-ref="3_4756811" class="rating-label" name="RateItem_4756811"><span class="rating-result"><span></span></span></span><a href="/Messages/4756811/Excellent-Code-Sample.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4729145_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4729145xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="4729145" parent="0" thread="4729145" href="/Messages/4729145/Exellent-Job.aspx">Exellent Job</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Marc-Olivier</span><span class="date">30-Dec-13  0:54</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4729145_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="2287487" msgid="4729145" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Hi Cedric<br />
Fantastic job, well illustrated and very well explained.<br />
Keep up the good work ! <img src="https://codeproject.global.ssl.fastly.net/script/Forums/Images/smiley_smile.gif" align="top" alt="Smile | :)" />   <img src="https://codeproject.global.ssl.fastly.net/script/Forums/Images/smiley_smile.gif" align="top" alt="Smile | :)" /> <br />
<div class="signature">Marc-Olivier<br />
Web and application developer<br />
marckwest001<a href="/Members/yahoo">@yahoo</a>.com</div><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4729145" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4729145" data-ref="3_4729145" class="rating-label" name="RateItem_4729145"><span class="rating-result"><span></span></span></span><a href="/Messages/4729145/Exellent-Job.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4483980_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4483980xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="4483980" parent="0" thread="4483980" href="/Messages/4483980/My-vote-of-5.aspx">My vote of 5</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Snorri Kristjansson</span><span class="date">30-Jan-13  0:27</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4483980_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="200400" msgid="4483980" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Very good article <img src="https://codeproject.global.ssl.fastly.net/script/Forums/Images/smiley_smile.gif" align="top" alt="Smile | :)" />  Code worked like advertised on SQL 2012 also.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4483980" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4483980" data-ref="3_4483980" class="rating-label" name="RateItem_4483980"><span class="rating-result"><span></span></span></span><a href="/Messages/4483980/My-vote-of-5.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4387389_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4387389xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="4387389" parent="0" thread="4387389" href="/Messages/4387389/My-vote-of-5.aspx">My vote of 5</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">nasserbr</span><span class="date">4-Oct-12  0:09</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4387389_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="8128046" msgid="4387389" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Effective and simple implementation of an audit trail!<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4387389" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4387389" data-ref="3_4387389" class="rating-label" name="RateItem_4387389"><span class="rating-result"><span></span></span></span><a href="/Messages/4387389/My-vote-of-5.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4387388_h0" class="header hover-row root">
<td class="subject-line normal vote-hi " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4387388xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="4387388" parent="0" thread="4387388" href="/Messages/4387388/char-amp-varchar-vs-ncahr-amp-nvarchar.aspx">char & varchar vs. ncahr & nvarchar</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">nasserbr</span><span class="date">4-Oct-12  0:08</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4387388_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="8128046" msgid="4387388" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Thank you for a great solution!<br />
<br />
I encountered one issue regarding the length of varchar and nvarchar columns.  GenerateAuditTrail generates columns with wrong length for nchar and nvarchar columns.<br />
<br />
<pre lang="sql"><span class="code-keyword">SELECT</span> b.name, c.name <span class="code-keyword">as</span> TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
        <span class="code-keyword">FROM</span> sysobjects a
        <span class="code-keyword">inner</span> <span class="code-keyword">join</span> syscolumns b <span class="code-keyword">on</span> a.id = b.id
        <span class="code-keyword">inner</span> <span class="code-keyword">join</span> systypes c <span class="code-keyword">on</span> b.xtype = c.xtype <span class="code-keyword">and</span> c.name <> <span class="code-string">'</span><span class="code-string">sysname'</span>
        <span class="code-keyword">WHERE</span> a.id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>)
        <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(a.id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>
        <span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span> b.colId</pre><br />
<br />
b.Length in this case returns bytes needed in order to contain the value and not the character length.  An varchar(10) has a length of 10 bytes.  nvarchar(10), however, has a byte length of 20 bytes.  In order to generate shadow tables with the right column length, you have to treat nachr and nvarchar columns differently.  <br />
<br />
<pre lang="sql"><span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">binary'</span>, <span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">varbinary'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>)
<span class="code-keyword">BEGIN</span>
    <span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
        <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">(max) '</span>
    <span class="code-keyword">ELSE</span>
        <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>
<span class="code-keyword">END</span>

<span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>)
<span class="code-keyword">BEGIN</span>
    <span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
        <span class="code-keyword">Set</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">(max) '</span>
    <span class="code-keyword">ELSE</span>
        <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast((<span class="code-sdkkeyword">@ColumnLength</span> / <span class="code-digit">2</span> ) <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">) '</span>
<span class="code-keyword">END</span></pre><br />
<br />
For ncahr and nvarchar fields, you need to divide the length by two.<br />
<br />
Best<br />
<br />
Nasser<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4387388" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4387388" data-ref="3_4387388" class="rating-label" name="RateItem_4387388"><span class="rating-result"><span></span></span></span><a href="/Messages/4387388/char-amp-varchar-vs-ncahr-amp-nvarchar.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4377062_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4377062xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="4377062" parent="0" thread="4377062" href="/Messages/4377062/My-vote-of-5.aspx">My vote of 5</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Kanasz Robert</span><span class="date">24-Sep-12  6:50</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4377062_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="7059641" msgid="4377062" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">Not bad<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4377062" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4377062" data-ref="3_4377062" class="rating-label" name="RateItem_4377062"><span class="rating-result"><span></span></span></span><a href="/Messages/4377062/My-vote-of-5.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4308162_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4308162xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="4308162" parent="0" thread="4308162" href="/Messages/4308162/Previous-post-of-modified-code.aspx">Previous post of modified code</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Adam Menkes</span><span class="date">12-Jul-12  12:57</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4308162_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="5645080" msgid="4308162" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I just realized after I posted it that the code may not work for fields with special characters. You can change the CHARINDEX() code to <br />
<pre lang="SQL">PATINDEX(<span class="code-string">'</span><span class="code-string">%[^a-z]%'</span>, <span class="code-sdkkeyword">@ColumnName</span>) > <span class="code-digit">0</span></pre><br />
<br />
<pre lang="SQL"><span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> PATINDEX(<span class="code-string">'</span><span class="code-string">%[^a-z]%'</span>, <span class="code-sdkkeyword">@ColumnName</span>) > <span class="code-digit">0</span> <span class="code-keyword">THEN</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">ELSE</span> <span class="code-sdkkeyword">@ColumnName</span>  <span class="code-keyword">END</span>  + <span class="code-string">'</span><span class="code-string">,'</span>

<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> PATINDEX(<span class="code-string">'</span><span class="code-string">%[^a-z]%'</span>, <span class="code-sdkkeyword">@ColumnName</span>) > <span class="code-digit">0</span> <span class="code-keyword">THEN</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">ELSE</span> <span class="code-sdkkeyword">@ColumnName</span>  <span class="code-keyword">END</span>  + <span class="code-string">'</span><span class="code-string"> '</span> + <span class="code-sdkkeyword">@ColumnType</span>
</pre><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4308162" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4308162" data-ref="3_4308162" class="rating-label" name="RateItem_4308162"><span class="rating-result"><span></span></span></span><a href="/Messages/4308162/Previous-post-of-modified-code.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F5128410_h0" class="header hover-row">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="9px" class="indent"><a name="xx5128410xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="5128410" parent="4308162" thread="4308162" href="/Messages/5128410/Re-Previous-post-of-modified-code.aspx">Re: Previous post of modified code</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Gert Hauan</span><span class="date">16-Sep-15  4:34</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F5128410_h1" class="content selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:9px;"><span class="voteform vertical" ownerid="74780" msgid="5128410" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2"><div class="parent"><a rel="nofollow" onclick="return forumCtrl_ctl00_MessageBoard.SwitchMessage(null,4308162);" href="/Messages/4308162/Previous-post-of-modified-code.aspx"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/arrow-up24.png" title="Go to Parent"  style="width:16px;height:16px;border:0" /></a></div>Thanks, I had this same problem.<br />
Do you know if the original creator of the procedure still is around to read/update the solution?<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4308162" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF5128410" data-ref="3_5128410" class="rating-label" name="RateItem_5128410"><span class="rating-result"><span></span></span></span><a href="/Messages/5128410/Re-Previous-post-of-modified-code.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4308159_h0" class="header hover-row root">
<td class="subject-line normal " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4308159xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_general.gif" alt="General" title="General" /></td><td class="subject hover-container"><a class="message-link" name="4308159" parent="0" thread="4308159" href="/Messages/4308159/Modified-version.aspx">Modified version</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">Adam Menkes</span><span class="date">12-Jul-12  12:48</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4308159_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="5645080" msgid="4308159" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I ran into an issue with tables with a LOT of columns, so I modified the code to remove '[ ]' where not needed as well as the section where it adds NULL/NOT NULL. Since NULL is the default, I removed it from the generated code.<br />
<br />
<pre lang="SQL"><span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> CHARINDEX(<span class="code-string">'</span><span class="code-string"> '</span>, <span class="code-sdkkeyword">@ColumnName</span>) = <span class="code-digit">0</span> <span class="code-keyword">THEN</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">ELSE</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">END</span> </pre><br />
<br />
<pre lang="SQL"><span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnNullable</span> = <span class="code-digit">0</span>
    <span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string"> NOT NULL'</span>

<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">, '</span>
</pre><br />
<br />
Here is the whole code:<br />
<pre lang="SQL"><span class="code-keyword">CREATE</span> <span class="code-keyword">PROCEDURE</span> [dbo].[sp_GenerateShadowTrail]
	<span class="code-sdkkeyword">@TableName</span> <span class="code-keyword">varchar</span>(<span class="code-digit">128</span>),
	<span class="code-sdkkeyword">@Owner</span> <span class="code-keyword">varchar</span>(<span class="code-digit">128</span>) = <span class="code-string">'</span><span class="code-string">dbo'</span>,
	<span class="code-sdkkeyword">@AuditNameExtention</span> <span class="code-keyword">varchar</span>(<span class="code-digit">128</span>) = <span class="code-string">'</span><span class="code-string">_Shadow'</span>,
	<span class="code-sdkkeyword">@DropAuditTable</span> <span class="code-keyword">bit</span> = <span class="code-digit">0</span>
<span class="code-keyword">AS</span>
<span class="code-keyword">BEGIN</span>

	<span class="code-comment">--</span><span class="code-comment"> Check if table exists</span>
	<span class="code-keyword">IF</span> <span class="code-keyword">not</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>)
	<span class="code-keyword">BEGIN</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">ERROR: Table does not exist'</span>
		<span class="code-keyword">RETURN</span>
	<span class="code-keyword">END</span>

	<span class="code-comment">--</span><span class="code-comment"> Check @AuditNameExtention</span>
	<span class="code-keyword">IF</span> <span class="code-sdkkeyword">@AuditNameExtention</span> <span class="code-keyword">is</span> <span class="code-keyword">null</span>
	<span class="code-keyword">BEGIN</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">ERROR: @AuditNameExtention cannot be null'</span>
		<span class="code-keyword">RETURN</span>
	<span class="code-keyword">END</span>

	<span class="code-comment">--</span><span class="code-comment"> Drop audit table if it exists and drop should be forced</span>
	<span class="code-keyword">IF</span> (<span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">@DropAuditTable</span> = <span class="code-digit">1</span>)
	<span class="code-keyword">BEGIN</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Dropping audit table ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>
		<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">drop table '</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span>)
	<span class="code-keyword">END</span>


	<span class="code-comment">--</span><span class="code-comment"> Declare cursor to loop over columns</span>
	<span class="code-keyword">DECLARE</span> TableColumns <span class="code-keyword">CURSOR</span> Read_Only
	<span class="code-keyword">FOR</span> <span class="code-keyword">SELECT</span> b.name, c.name <span class="code-keyword">as</span> TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
		<span class="code-keyword">FROM</span> sysobjects a 
		<span class="code-keyword">inner</span> <span class="code-keyword">join</span> syscolumns b <span class="code-keyword">on</span> a.id = b.id 
		<span class="code-keyword">inner</span> <span class="code-keyword">join</span> systypes c <span class="code-keyword">on</span> b.xtype = c.xtype <span class="code-keyword">and</span> c.name <> <span class="code-string">'</span><span class="code-string">sysname'</span> 
		<span class="code-keyword">WHERE</span> a.id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">]'</span>) 
		<span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(a.id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span> 
		<span class="code-keyword">ORDER</span> <span class="code-keyword">BY</span> b.colId

	<span class="code-keyword">OPEN</span> TableColumns

	<span class="code-comment">--</span><span class="code-comment"> Declare temp variable to fetch records into</span>
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">nvarchar</span>(<span class="code-digit">250</span>)
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">nvarchar</span>(<span class="code-digit">100</span>)
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">smallint</span>
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnNullable</span> <span class="code-keyword">int</span>
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnCollation</span> sysname
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnPrecision</span> <span class="code-keyword">tinyint</span>
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ColumnScale</span> <span class="code-keyword">tinyint</span>

	<span class="code-comment">--</span><span class="code-comment"> Declare variable to build statements</span>
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@CreateStatement</span> <span class="code-keyword">nvarchar</span>(MAX)
	<span class="code-keyword">DECLARE</span> <span class="code-sdkkeyword">@ListOfFields</span> <span class="code-keyword">nvarchar</span>(<span class="code-digit">4000</span>)
	<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-string">'</span><span class="code-string">'</span>


	<span class="code-comment">--</span><span class="code-comment"> Check if audit table exists</span>
	<span class="code-keyword">IF</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsUserTable'</span>) = <span class="code-digit">1</span>)
	<span class="code-keyword">BEGIN</span>
		<span class="code-comment">--</span><span class="code-comment"> AuditTable exists, update needed</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Table already exists. Only triggers will be updated.'</span>

		<span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
		<span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>

		<span class="code-keyword">WHILE</span> <span class="code-systemcall">@@FETCH_STATUS</span> = <span class="code-digit">0</span>
		<span class="code-keyword">BEGIN</span>
			<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">text'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">ntext'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">image'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">timestamp'</span>)
			<span class="code-keyword">BEGIN</span>
			
				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> CHARINDEX(<span class="code-string">'</span><span class="code-string"> '</span>, <span class="code-sdkkeyword">@ColumnName</span>) = <span class="code-digit">0</span> <span class="code-keyword">THEN</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">ELSE</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">END</span> + <span class="code-string">'</span><span class="code-string">,'</span>
			<span class="code-keyword">END</span>

			<span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
			<span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>

		<span class="code-keyword">END</span>
	<span class="code-keyword">END</span>
	<span class="code-keyword">ELSE</span>
	<span class="code-keyword">BEGIN</span>
		<span class="code-comment">--</span><span class="code-comment"> AuditTable does not exist, create new</span>

		<span class="code-comment">--</span><span class="code-comment"> Start of create table</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">CREATE TABLE ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]('</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">AuditId bigint IDENTITY (1,1) NOT NULL,'</span>

		<span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
		<span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>
		
		<span class="code-keyword">WHILE</span> <span class="code-systemcall">@@FETCH_STATUS</span> = <span class="code-digit">0</span>
		<span class="code-keyword">BEGIN</span>
			<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">text'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">ntext'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">image'</span> <span class="code-keyword">and</span> <span class="code-sdkkeyword">@ColumnType</span> <> <span class="code-string">'</span><span class="code-string">timestamp'</span>)
			<span class="code-keyword">BEGIN</span>
				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@ListOfFields</span> = <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> CHARINDEX(<span class="code-string">'</span><span class="code-string"> '</span>, <span class="code-sdkkeyword">@ColumnName</span>) = <span class="code-digit">0</span> <span class="code-keyword">THEN</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">ELSE</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">END</span> + <span class="code-string">'</span><span class="code-string">,'</span>
		
				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-keyword">CASE</span> <span class="code-keyword">WHEN</span> CHARINDEX(<span class="code-string">'</span><span class="code-string"> '</span>, <span class="code-sdkkeyword">@ColumnName</span>) = <span class="code-digit">0</span> <span class="code-keyword">THEN</span> <span class="code-sdkkeyword">@ColumnName</span> <span class="code-keyword">ELSE</span> QUOTENAME(<span class="code-sdkkeyword">@ColumnName</span>) <span class="code-keyword">END</span> + <span class="code-string">'</span><span class="code-string"> '</span> + <span class="code-sdkkeyword">@ColumnType</span> 
				
				<span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">binary'</span>, <span class="code-string">'</span><span class="code-string">char'</span>, <span class="code-string">'</span><span class="code-string">nchar'</span>, <span class="code-string">'</span><span class="code-string">nvarchar'</span>, <span class="code-string">'</span><span class="code-string">varbinary'</span>, <span class="code-string">'</span><span class="code-string">varchar'</span>)
				<span class="code-keyword">BEGIN</span>
					<span class="code-keyword">IF</span> (<span class="code-sdkkeyword">@ColumnLength</span> = -1)
						<span class="code-keyword">Set</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">(max) '</span>	 	
					<span class="code-keyword">ELSE</span>
						<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnLength</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">)'</span>	 	
				<span class="code-keyword">END</span>
		
				<span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnType</span> <span class="code-keyword">in</span> (<span class="code-string">'</span><span class="code-string">decimal'</span>, <span class="code-string">'</span><span class="code-string">numeric'</span>)
					<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">('</span> + cast(<span class="code-sdkkeyword">@ColumnPrecision</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">,'</span> + cast(<span class="code-sdkkeyword">@ColumnScale</span> <span class="code-keyword">as</span> <span class="code-keyword">varchar</span>(<span class="code-digit">10</span>)) + <span class="code-string">'</span><span class="code-string">)'</span>
		
				<span class="code-comment">--</span><span class="code-comment">IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')</span>
					<span class="code-comment">--</span><span class="code-comment">SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '</span>
		
				<span class="code-keyword">IF</span> <span class="code-sdkkeyword">@ColumnNullable</span> = <span class="code-digit">0</span>
					<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string"> NOT NULL'</span>	 	
		
				<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">, '</span>	
			<span class="code-keyword">END</span>

			<span class="code-keyword">FETCH</span> Next <span class="code-keyword">FROM</span> TableColumns
			<span class="code-keyword">INTO</span> <span class="code-sdkkeyword">@ColumnName</span>, <span class="code-sdkkeyword">@ColumnType</span>, <span class="code-sdkkeyword">@ColumnLength</span>, <span class="code-sdkkeyword">@ColumnNullable</span>, <span class="code-sdkkeyword">@ColumnCollation</span>, <span class="code-sdkkeyword">@ColumnPrecision</span>, <span class="code-sdkkeyword">@ColumnScale</span>
		<span class="code-keyword">END</span>

		<span class="code-comment">--</span><span class="code-comment"> Add audit trail columns</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">AuditAction char(1),'</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">AuditDate datetime NOT NULL ,'</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">AuditUser varchar(64),'</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">AuditApp varchar(128))'</span> 

		<span class="code-comment">--</span><span class="code-comment"> Create audit table</span>
		<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Creating audit table ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">]'</span>
		<span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>)

		<span class="code-comment">--</span><span class="code-comment"> Set primary key and default values</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-string">'</span><span class="code-string">ALTER TABLE '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.['</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">] ADD '</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">_AuditDate] DEFAULT (getdate()) FOR AuditDate,'</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">_AuditUser] DEFAULT (suser_sname()) FOR AuditUser,CONSTRAINT [PK_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">] PRIMARY KEY  CLUSTERED '</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">([AuditId])  ON [PRIMARY], '</span>
		<span class="code-keyword">SET</span> <span class="code-sdkkeyword">@CreateStatement</span> = <span class="code-sdkkeyword">@CreateStatement</span> + <span class="code-string">'</span><span class="code-string">CONSTRAINT [DF_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">_AuditApp]  DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'</span>

		<span class="code-keyword">EXEC</span> (<span class="code-sdkkeyword">@CreateStatement</span>)
	<span class="code-keyword">END</span>

	<span class="code-keyword">CLOSE</span> TableColumns
	<span class="code-keyword">DEALLOCATE</span> TableColumns

	<span class="code-comment">/*</span><span class="code-comment"> Drop Triggers, if they exist */</span>
	<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Dropping triggers'</span>
	<span class="code-keyword">IF</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
		<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">drop trigger ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert]'</span>)
		<span class="code-comment">--</span><span class="code-comment">PRINT ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')</span>

	<span class="code-keyword">IF</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
		<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">drop trigger ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update]'</span>)
		<span class="code-comment">--</span><span class="code-comment">PRINT ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')</span>

	<span class="code-keyword">IF</span> <span class="code-keyword">exists</span> (<span class="code-keyword">SELECT</span> * <span class="code-keyword">FROM</span> dbo.sysobjects <span class="code-keyword">WHERE</span> id = object_id(N<span class="code-string">'</span><span class="code-string">['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete]'</span>) <span class="code-keyword">and</span> <span class="code-sdkkeyword">OBJECTPROPERTY</span>(id, N<span class="code-string">'</span><span class="code-string">IsTrigger'</span>) = <span class="code-digit">1</span>) 
		<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">drop trigger ['</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">].[tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete]'</span>)
		<span class="code-comment">--</span><span class="code-comment">PRINT ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')</span>

	<span class="code-comment">/*</span><span class="code-comment"> Create triggers */</span>
	<span class="code-keyword">PRINT</span> <span class="code-string">'</span><span class="code-string">Creating triggers'</span> 
	<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Insert ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR INSERT AS INSERT INTO '</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">('</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">AuditAction) SELECT '</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">''I'' FROM Inserted'</span>)
	<span class="code-comment">--</span><span class="code-comment">PRINT ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')</span>

	<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Update ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR UPDATE AS INSERT INTO '</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">('</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">AuditAction) SELECT '</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">''U'' FROM Inserted'</span>)

	<span class="code-keyword">EXEC</span> (<span class="code-string">'</span><span class="code-string">CREATE TRIGGER tr_'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string">_Delete ON '</span> + <span class="code-sdkkeyword">@Owner</span> + <span class="code-string">'</span><span class="code-string">.'</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-string">'</span><span class="code-string"> FOR DELETE AS INSERT INTO '</span> + <span class="code-sdkkeyword">@TableName</span> + <span class="code-sdkkeyword">@AuditNameExtention</span> + <span class="code-string">'</span><span class="code-string">('</span> +  <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">AuditAction) SELECT '</span> + <span class="code-sdkkeyword">@ListOfFields</span> + <span class="code-string">'</span><span class="code-string">''D'' FROM Deleted'</span>)


<span class="code-keyword">END</span></pre><br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4308159" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4308159" data-ref="3_4308159" class="rating-label" name="RateItem_4308159"><span class="rating-result"><span></span></span></span><a href="/Messages/4308159/Modified-version.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr id="F4182564_h0" class="header hover-row root">
<td class="subject-line normal vote-hi " width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="0px" class="indent"><a name="xx4182564xx"></a><img height="16px" width="16px" align="top" src="https://codeproject.freetls.fastly.net/script/Forums/Images/msg_question.gif" alt="Question" title="Question" /></td><td class="subject hover-container"><a class="message-link" name="4182564" parent="0" thread="4182564" href="/Messages/4182564/Column-Name-with-space.aspx">Column Name with space.</a> <a onclick="return forumCtrl_ctl00_MessageBoard.Pin(this);" rel="nofollow" href="#" title="Click to pin message"><img src="https://codeproject.freetls.fastly.net/script/Forums/Images/pin.png" border="0" align="top" alt="Pin" width="13px" height="13px" /></a><div class="mobile-only">
	<span class="author">deadtrees</span><span class="date">6-Mar-12  12:36</span>
</div></td>
</tr>
</table></td>
</tr><tr id="F4182564_h1" class="content root selected" style="display:none;">
<td class="normal" width="100%"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td class="indent align-right" style="width:0px;"><span class="voteform vertical" ownerid="8705881" msgid="4182564" votingType="GoodOrBad"></span></td><td class="text"><table border="0" cellpadding="0" cellspacing="5px" width="100%">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td colspan="2">I have a column name with a space in it [Name Here] and when I execute the SP it chokes on creating the triggers.  I went to the trigger code and added +'['+@tablename+']'+ but that doesn't seem to do the trick. It also chokes on the audit name exension. Any advice?<br />
<br />
SQL 2008<br />
<br />
Table already exists. Only triggers will be updated.<br />
Dropping triggers<br />
Creating triggers<br />
Msg 102, Level 15, State 1, Procedure tr_, Line 1<br />
Incorrect syntax near 'CaseDetails'.<br />
Msg 102, Level 15, State 1, Procedure tr_, Line 1<br />
Incorrect syntax near '_shadow'.<br /></td>
</tr><tr class="footer" style="vertical-align:top;">
<td><a href="https://www.codeproject.com/script/Membership/LogOn.aspx?rp=/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414">Sign In</a>·<wbr><a href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&tid=4182564" title="View Thread">View Thread</a></td><td style="text-align:right;"><span id="MVF4182564" data-ref="3_4182564" class="rating-label" name="RateItem_4182564"><span class="rating-result"><span></span></span></span><a href="/Messages/4182564/Column-Name-with-space.aspx" title="Get permanent link" style="border:0 !important"><img src="https://codeproject.freetls.fastly.net/App_Themes/CodeProject/Img/link32.png" style="width:14px;height:14px;border:0" /></a> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr><tr>
<td><table width="100%" cellpadding="2px" cellspacing="0">
<tr class="footer">
<td><span class="last-visit desktop-only tablet-only"></span></td><td><a rel="nofollow" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&prof=True&view=Normal&fr=1">Refresh</a></td><td style="text-align:right;white-space:nowrap;"><input id="_mbnUrl" type="hidden" value="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&prof=True&view=Normal&fr=26" /><span class="nav-link selected">1</span><a class="nav-link" name="Frm_HoverNL" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&prof=True&view=Normal&fr=26#xx0xx">2</a><a class="nav-link" name="Frm_HoverNL" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&prof=True&view=Normal&fr=51#xx0xx">3</a> <a class="nav-link" name="Frm_HoverNL" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?fid=476191&df=90&mpp=25&sort=Position&spc=Relaxed&select=4167414&prof=True&view=Normal&fr=26#xx0xx">Next »</a></td>
</tr>
</table></td>
</tr>
</table>
</div>
				

			</div>
			

			
		    <div id="ctl00_LeftSideBar" class="article-left-sidebar">

                <div id="ctl00_StickyBlock" class="article-left-sidebar-inner">
                    
    		        

<div class="tabs">
	<div class=" selected">Article</div>
<div><a href="/script/Articles/Statistics.aspx?aid=21068">View Stats</a></div>
<div><a href="/script/Articles/ListVersions.aspx?aid=21068">Revisions</a></div>
<div><a href="#_comments">Comments (64)</a></div>
<div>Posted 27 Oct 2007</div>


    
</div>



			        

			        <div>
                        <h4 id="ctl00_TaggedAsWrapper">Tagged as</h4>
			            <span id="ctl00_TagList_TagWrp" class="tags vertical">

	
	

	
	<span id="ctl00_TagList_VisibleTags"><div class="t"><a rel="tag" href="/Tags/sharp" data-id="81">C#</a></div><div class="t"><a rel="tag" href="/Tags/SQL" data-id="93">SQL</a></div><div class="t"><a rel="tag" href="/Tags/Windows" data-id="94">Windows</a></div><div class="t"><a rel="tag" href="/Tags/.NET" data-id="98">.NET</a></div><div class="t"><a rel="tag" href="/Tags/SQL-Server" data-id="101">SQL-Server</a></div><div class="t"><a rel="tag" href="/Tags/Visual-Studio" data-id="103">Visual-Studio</a></div></span> 

	
	
</span>


			        </div>

                    <div id="ctl00_VerticalStatsWrapper" class="stats">
                    <h4>Stats</h4>
                    <div id="ctl00_VerticalStats"><div class="stats"><div>270.9K views</div><div>8.4K downloads</div><div>133 bookmarked</div></div></div>
                    </div>

                    
                </div>
		    </div>
            

            
            <div id="ctl00_RightSideBar" class="article-right-sidebar">

                <div id="RHSticky" class="container-article-info-sticky">
        		    

<div class="article-summary">

    
    

</div>

				    <div id="ctl00_SectionSponsor" class="padded-top" style="width:160px;margin:10px auto">
					    <div class="msg-160x200" data-format="160x200" data-type="ad" data-publisher="lqm.codeproject.site" data-zone="Database/Database/SQL-Server"  data-country='US' data-showindicator="true"  data-tags='C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,rating4.5'><noscript><a href="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=160x200&c=784999"><img src="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=160x200&c=784999"  width="160px" height="200px" target="_blank"/></a></noscript></div>
				    </div>

				    <div style="width:160px;margin: 10px auto;">
					    <div class="msg-160x600" data-format="160x600" data-type="ad" data-publisher="lqm.codeproject.site" data-zone="Database/Database/SQL-Server"  data-country='US' data-tags='C#, SQL, Windows, .NET, SQL-Server, Visual-Studio, Architect, DBA, Dev, Design, Intermediate,rating4.5'><noscript><a href="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=160x600&c=784999"><img src="https://pubads.g.doubleclick.net/gampad/jump?iu=/6839/lqm.codeproject.site/Database/Database/SQL-Server&sz=160x600&c=784999"  width="160px" height="600px" target="_blank"/></a></noscript></div>
				    </div>

                    

				    

				    

                    <div class="anchorLink gototop" id="gototop">
                        <a id="ctl00_GoToTop" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL?msg=4167414#_articleTop">Go to top</a>
                    </div>

			    </div>
            </div>
            

        </div>
        

		
		<div class="theme1-background" style="height:2px;margin:5px 0" id="stickyStop"></div>

		<div class="site-footer">
			<div class="align-left">
				<a id="ctl00_PermaLink" href="/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL">Permalink</a><br>
				<a id="ctl00_AdvertiseLink" href="http://developermedia.com/">Advertise </a><br>
				<a id="ctl00_PrivacyLink" href="/info/privacy.aspx">Privacy</a><br>
    			<a id="ctl00_CookiePolicyLink" href="/info/cookie.aspx">Cookies</a><br>
                <a id="ctl00_TermsOfUseLink" href="/info/TermsOfUse.aspx">Terms of Use</a><br>
			</div>

            <div class="align-center">
				
				

				

	            

                <br>
			</div>
                
			<div class="align-right">
				Article Copyright 2007 by Cedric Baelemans<br />Everything else
				Copyright © <a href="mailto:webmaster@codeproject.com">CodeProject</a>, 1999-2020 <br />
                <br>
				Web02 
				2.8.200223.1<br>
			</div>
		</div>
		

		<br clear="all" />
		
			

	</div> 
	</div> 
</div>



<img id="ctl00_Audience" src="https://pubads.g.doubleclick.net/activity;dc_iu=/6839/DFPAudiencePixel;ord='637181444752972415';dc_seg=33704410?" style="border-width:0px;height:1px;width:1px;" />



<script type="text/javascript"> // IMMEDIATE script

function Join() {
                                     document.location.href = 'https://www.codeproject.com/script/Membership/Modify.aspx';
                                     return false;
                                  }

var kpucligmwp = function(n){var t=!1;n=n||{};var r=n.RetrievalServiceUrl||"/script/content/webservices/RetrieveContent.aspx/GetContent",u=n.RetrievalServiceRetries||3,f=n.RetrievalServiceTimeout||5e3,e=function(){t||(t=!0,$(window).on("resize scroll",i),i())},i=function(){if(typeof DMAds=="undefined"){var n=$('[data-type="ad"]');n.each(function(){var n=$(this),u=n.attr("data-format")||"",f=n.attr("data-publisher")||"",e=n.attr("data-zone")||"",t=n.attr("data-loadonview"),i=n.attr("data-noadx"),h=n.attr("data-tags")||"",c=n.find("iframe").length!==0||n.find("img").length!==0,r=n.attr("data-processed");t=typeof t=="string"&&t.toLowerCase()==="true";i=typeof i=="string"&&i.toLowerCase()==="true";r=typeof r=="string"&&r.toLowerCase()==="true";r||c||t&&!s(this)||(o(n,u,f,e,i,h),n.attr("data-processed","true"))})}},o=function(n,t,i,e,o,s){var h=new $.Deferred,c=JSON.stringify({format:t,publisherCode:i,zone:e,noAdX:o,keywords:s});$.ajax({url:r,cache:!1,type:"POST",data:c,contentType:"application/json; charset=utf-8",dataType:"json",tryCount:0,retryLimit:u,timeout:f}).done(function(t){var i=t?t.d:null;h.resolve(i); i && i.html && n.html(i.html)}).fail(function(n,t){h.reject(t)})},s=function(n){var t,f,e,o;if(!h(n))return!1;var u=!1,i=0,r=0;try{t=n.getBoundingClientRect();typeof innerWidth=="number"?(i=window.innerWidth,r=window.innerHeight):document.documentElement&&(document.documentElement.clientWidth||document.documentElement.clientHeight)?(i=document.documentElement.clientWidth,r=document.documentElement.clientHeight):document.body&&(document.body.clientWidth||document.body.clientHeight)&&(i=document.body.clientWidth,r=document.body.clientHeight);f=-200;t.top===t.bottom&&(f+=n.height*-1);e=t.top>=0&&t.top-200<=r||t.top<=0&&t.top>=f;o=i>t.left&&t.right>=0;u=e&&o}catch(s){u=!0}return u},h=function(n){var t=!1,i;return n&&n.style.position!=="fixed"&&n.offsetParent?t=!0:n&&n.style.position==="fixed"&&(i=window.getComputedStyle(n),t=i&&i.display!=="none"),t&&c()},c=function(){var t=!0,n="";return typeof document.hidden!="undefined"?n="hidden":typeof document.mozHidden!="undefined"?n="mozHidden":typeof document.msHidden!="undefined"?n="msHidden":typeof document.webkitHidden!="undefined"&&(n="webkitHidden"),n!==null&&document[n]&&(t=!1),t};return{init:e}};
document.addEventListener('DOMContentLoaded', function() {
var n=window.XMLHttpRequest?new XMLHttpRequest:new ActiveXObject("MSXML2.XMLHTTP"),t;n.onreadystatechange=function(){try{if(n.readyState==4)if(n.status==200){var t=document.createElement("script");t.type="text/javascript";t.innerHTML=n.responseText;document.getElementsByTagName("body")[0].appendChild(t)}else(new kpucligmwp).init()}catch(i){(new kpucligmwp).init()}};try{t='/a.min.js?v=1.3';n.open("GET",t,!0);n.send()}catch(i){(new kpucligmwp).init()}

  setTimeout(function(){ 
    $('div[data-type="ad"][data-showindicator="true"]').each(function( index ) {
      if ($(this).height() > 0) {
        $(this).prepend('<div class="small-text muted">Advertisement</div>');
      }
    });
  }, 2000);

});

function PostBack_ctl00_RateArticle_RateItemWrapper() {
  return rateItem(null, 21068,2,1,true,true,0,'LargeStars', null, 671546);
}

function getVotesHistogram(objectId, objectTypeId, containerId, loadingId) {
                        if (!$('#' + containerId).attr('alreadyRequested')){
                            $.ajax({
                                url: '/script/Ratings/Ajax/Histogram.aspx?obid=' + objectId + '&obtid='+objectTypeId+'&wd=130',
                            })
                            .done(function (data) {
                                $('#' + containerId).html(data);
                                $('#' + loadingId).hide();
                            });

                            $('#' + containerId).attr('alreadyRequested', 'true');
                        }
                    }

document.addEventListener('DOMContentLoaded', function() {
  $('#ctl00_RateArticle_SubmitRateBtn').hide();
});
function ChkRtctl00_RateArticle(){}

var forumConfig_ctl00_MessageBoard;
var forumCtrl_ctl00_MessageBoard;
var forumCtrl_ctl00_MessageBoard_forumMsgFlag;

var subscriptionConfig_ctl00_MessageBoard = {
 ParentId      : 'ctl00_MessageBoard_SubscribeDiv',
 NewId         : 'ctl00_MessageBoard_SubscribeNew',
 ReplyId       : 'ctl00_MessageBoard_SubscribeReplies',
 InstantId     : 'ctl00_MessageBoard_SubscribeInstant',
 DailyId       : 'ctl00_MessageBoard_SubscribeDaily',
 MessageTypeId : 'ctl00_MessageBoard_MessageType',
 GetInfoUrl    : '/script/Forums/WebServices/ForumSubscriptionServices.aspx/GetSubscriptions',
 UpdateInfoUrl : '/script/Forums/WebServices/ForumSubscriptionServices.aspx/UpdateSubscription',
 ForumId       : 476191,
};
var subscriptions_ctl00_MessageBoard;

var mliilhn = false;
var DMReportsOK = null;
function showDMAlert() { 
  var $div = $('div.msg-728x90');
  $div.append($('<img src="/images/alert-top-block.gif" style="right:0;position:absolute;z-index:0">'));
}
function onDMcallBack(event)
{
   if (event.originalEvent) event = event.originalEvent;
   jQuery.support.cors = true;
   if (event.data === 'DM-disabled') DMReportsOK = false;
   else if (event.data=='DM-enabled') DMReportsOK = true;
   else if (typeof event.data == 'object' && event.data.sender &&             event.data.sender == 'DeveloperMedia' && event.data.application=='apps' &&
            event.data.action == 'reportEnabled') {
            DMReportsOK = event.data.data.enabled;
   }
   wrxrdtwlef = !DMReportsOK;
}
function checkLoaded() { 
 var showBlockMsg = false;
 var block        = 'None';
 if (block === 'None' && typeof DMAds === 'undefined')
     block = 'ResourceBlocked';
 if (block === 'None' && !document.getElementById('adbp-tester'))     block = 'ResourceBlocked';
 if (block === 'None' && DMReportsOK === false)     block = 'DomModified';
 wrxrdtwlef = block != 'None';
 if (mliilhn) {
  $.ajax({ url:'/script/Content/Ajax/VS.aspx',    data:{'cadm':block},     cache:false, async:true });
};
 if (showBlockMsg && block != 'None')
    showDMAlert();
}
</script>

<script type="text/javascript"> // DEFERRED script

document.addEventListener('DOMContentLoaded', function() {

	processCodeBlocks.Initialise('#contentdiv');
	
	$('.author-wrapper .description').shorten({showChars: 400});
	
	anchorAnimate();
	$('#RHSticky').sticky($('#stickyStop'));
	$('#ctl00_StickyBlock').sticky($('#stickyStop'));
	
	$('#__EVENTVALIDATION').attr('autocomplete', 'off');
	
	$('#joinBtn').css('visibility', 'visible');
	
	$('.oauth').click(function () {
	    $this = $(this);
	    href = $this.attr('href');
	    var myWindow = window.open(href, 'popup',
	                              'width=800,height=600,location=0,menubar=0,resizeable=0,scrollbars=0,toolbar=0');
	    myWindow.focus();
	    var timer = setInterval(function () {
	        if (myWindow.closed) {
	            clearInterval(timer);
	            // window.location.reload(); // May do a POST reload, shows a warning
	            window.location = window.location; // force a GET reload
	        }
	    }, 200);
	    return false;
	});
	
	var oSrchFlt = false, oSrchBox=false, srchBoxFoc=false;
	var sbar     = $('#sb_tb');
	var sfilter  = $('#SearchFilter');
	
	if (typeof(InitWatermark) !== 'undefined')
	  InitWatermark('sb_tb', 'Search for articles, questions, tips');
	if (sbar && sfilter) {
	  sfilter.removeClass('popup'); sfilter.hide(); sfilter.removeClass('open');
	  sbar.blur(function() {
	    if (!oSrchFlt) { sfilter.hide(); sfilter.removeClass('open'); }
	    srchBoxFoc = false;
	  });
	  sbar.focus(function() {
	    oSrchFlt   = false;
	    srchBoxFoc = true;
	    sfilter.show();
	    sfilter.addClass('open');
	  });
	  sbar.mouseleave(function()    { oSrchBox = false; });
	  sbar.mouseover(function()     { oSrchBox = true; });
	  sfilter.mouseleave(function() {
	    oSrchFlt=false;
	    if (!srchBoxFoc && !oSrchBox) { sfilter.hide(); sfilter.removeClass('open'); }
	  });
	  sfilter.mouseover(function()  { oSrchFlt = true; });
	}
	
	$('#clear-rate_ctl00_RateArticle_RSU').click(function () {    $('#ctl00_RateArticle_RSU').hide(); return false;});
	
	new starRating('#ctl00_RateArticle_RateItemWrapper', PostBack_ctl00_RateArticle_RateItemWrapper,'ctl00_RateArticle');
	  $('#ctl00_RateArticle_VR').mouseenter(function() {     getVotesHistogram(21068,2   ,'ctl00_RateArticle_HistogramR','ctl00_RateArticle_LoadingR');
	  });
	
	forumConfig_ctl00_MessageBoard = {
	    objectName          : 'forumCtrl_ctl00_MessageBoard',
	    messageTypeId       : 3,
	    searchPrompt        : 'Search Comments',
	    smoothScroll        : true,
	    allowReporting      : false,
	    allowRating         : false,
	    allowRatingDisplay  : true,
	    forumDir            : '/script/Forums/',
	    staticServer        : 'https://codeproject.freetls.fastly.net',
	    switchMsgCallback   : null,
	    getVoteFormMethod   : getVoteFormHtml,
	    getReportFormMethod : getReportFormHtml,
	    voteHiThreshold     : 5 - 0.5,
	    getRatingUrl        : '/script/Ratings/Ajax/GetRatings.aspx',
	    getRatingRefKey     : 'obrs',
	    reportOptions       : [
	       { reportTypeId : 19, name : 'Abusive / Trolling'},
	       { reportTypeId : 20, name : 'Inappropriate'},
	       { reportTypeId : 5, name : 'Spam'},
	    ],
	};
	forumCtrl_ctl00_MessageBoard = new ForumControl(forumConfig_ctl00_MessageBoard, 4167414);
	forumCtrl_ctl00_MessageBoard.Initialise();
	forumCtrl_ctl00_MessageBoard.EnsureMessageVisible(4167414, true);
	
	subscriptions_ctl00_MessageBoard = new Subscriptions(subscriptionConfig_ctl00_MessageBoard);
	subscriptions_ctl00_MessageBoard.initialise();
	
	cookieconsent.initialise({
	                                    enabled : true,
	                                    cookie  : { domain: 'codeproject.com' },
	                                    palette : {
	                                        popup: { background: '#ff9900' },
	                                        button: { background: '#f5d948' }
	                                    },
	                                    law :  {
	                                        showForAllRegions : true, 
	                                        countryCode : 'US' 
	                                    },
	                                    theme: 'edgeless',
	                                    type : 'opt-in',
	                                    content: {
	                                        message: 'Like every other website we use cookies. By using our site you acknowledge that you have read and understand our <a href=\'/info/cookie.aspx\'>Cookie Policy</a>, <a href=\'/info/privacy.aspx\'>Privacy Policy</a>, and our <a href=\'/info/TermsOfUse.aspx\'>Terms of Service</a>.',
	                                        href:    'https://www.codeproject.com/info/privacy.aspx',
	                                        dismiss: 'Ask me later'
	                                    },
	                                    revokable:true,
	                                    onStatusChange: function(status) {
	                                        $.ajax({
	                                		    dataType  : 'json',
	                                            data: JSON.stringify({ allowCookies : this.hasConsented() }),
	                                            url: '/script/membership/webservices/LoggedOnMemberservices.aspx/SetCookieConsent',
	                                            cache: false,
	                                            type: 'POST',
	                                            contentType: 'application/json'
	                                        })
	                                        console.log(this.hasConsented() ? 'enable cookies' : 'disable cookies');
	                                        console.log(this.hasAnswered() ? 'has answered' : 'did not answer');
	                                    },
	                                });
	
	$(window).on('message', onDMcallBack);
	setTimeout(checkLoaded, 4000);

})
</script>








</body>

</html>

HEADER-CODE:














IP 76.74.234.210 www.codeproject.com

Website:www.codeproject.com
IP:76.74.234.210
County:CA Canada
Region:ON Ontario
City:M6G Toronto
Timezone:America/Toronto
html
html5
CSS
CSS3
CSS GRID
Java
https://www.radio.de/s/wmse | Team | Wirtschaftsförderung Mecklenburgische Seenplatte | https://www.wm.de/de/wmse/unternehmen/standorte.html | Jug Handle Creek Farm & Nature Center – A non-profit, nature & educational center, with overnight facility | How to make Easter special this year for your kids - Direct Sales, Party Plan and Network Marketing Companies Member Article By Jennifer Darley | https://www.gog.com/forum/mafia/mafia_iii_bug_classic_outfit_il_duca_disappeared | Linux | Presentation Design | A Friend Agency | Switzerland | Der Name Anjay und seine Namensbedeutung | APHSA National Health and Human Services 2018 Summit | National Council on Crime & Delinquency | https://verialg.iti.kit.edu/download/DP-01.pdf | Blundon, West Virginia - Wikipedia | Englisch ⇔ Deutsch Wörterbuch - leo.org: Startseite | Dula International - | ALZFORUM | NETWORKING FOR A CURE | https://ecs.struktol.net/pdf/Brochure_Antifoams_Technical_Applications.pdf