C# Online Compiler | .NET Fiddle

<no name> by Anonymous

using System;
using System.Collections.Generic;
using System.Linq;

namespace abc.ArgumentValidation
{
	/// <summary>
	/// 
	/// </summary>
	public static class ArgumentValidator
	{
		internal sealed class ValidatedNotNullAttribute : Attribute
		{}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static T CheckForNullReference<T>([ValidatedNotNull][NoEnumeration] T parameterValue, string parameterName) where T : class
		{
			CheckParameterName(parameterName);

			if (parameterValue == null)
			{
				throw new ArgumentNullException(parameterName);
			}

			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <param name="message"></param>
		/// <returns></returns>
		public static T CheckForNullReference<T>([ValidatedNotNull][NoEnumeration] T parameterValue, string parameterName, string message) where T : class
		{
			CheckParameterName(parameterName);

			if (parameterValue == null)
			{
				throw new ArgumentNullException(parameterName, message);
			}

			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static T CheckForNullReference<T>(T? parameterValue, string parameterName) where T : struct
		{
			CheckParameterName(parameterName);

			if (!parameterValue.HasValue)
			{
				throw new ArgumentNullException(parameterName);
			}

			return parameterValue.Value;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static string CheckForNullOrEmptyString([ValidatedNotNull] string parameterValue, string parameterName)
		{
			return CheckForNullOrEmptyString(parameterValue, parameterName, "Value cannot be null or empty.");
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <param name="message"></param>
		/// <returns></returns>
		public static string CheckForNullOrEmptyString([ValidatedNotNull] string parameterValue, string parameterName, string message)
		{
			CheckParameterName(parameterName);

			if (string.IsNullOrEmpty(parameterValue))
			{
				throw new ArgumentException(message, parameterName);
			}
			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="throwIf"></param>
		/// <param name="parameterName"></param>
		/// <param name="message"></param>
		public static void ConditionalArgumentException(bool throwIf, string parameterName, string message)
		{
			CheckParameterName(parameterName);

			if (throwIf)
			{
				throw new ArgumentException(message, parameterName);
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static T[] CheckArrayNotNullOrEmpty<T>([ValidatedNotNull] T[] parameterValue, string parameterName)
		{
			CheckForNullReference(parameterValue, parameterName);

			if (parameterValue.Length == 0)
			{
				throw new ArgumentException("Array cannot be empty.", parameterName);
			}
			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <param name="minSize"></param>
		/// <returns></returns>
		public static T[] CheckArrayAboveMinimumLength<T>([ValidatedNotNull] T[] parameterValue, string parameterName, int minSize)
		{
			CheckForNullReference(parameterValue, parameterName);

			if (parameterValue.Length < minSize)
			{
				throw new ArgumentException(string.Format("Array was expected to contain at least [{0}] element(s) but actually contained [{1}]", minSize, parameterValue.Length), parameterName);
			}

			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <param name="length"></param>
		/// <returns></returns>
		public static T[] CheckArrayLength<T>([ValidatedNotNull] T[] parameterValue, string parameterName, int length)
		{
			CheckForNullReference(parameterValue, parameterName);

			if (parameterValue.Length != length)
			{
				throw new ArgumentException(string.Format("Array was expected to contain [{0}] element(s) but actually contained [{1}]", length, parameterValue.Length), parameterName);
			}

			return parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static T CheckTypeCorrectAndCast<T>(object parameterValue, string parameterName) where T : class
		{
			CheckForNullReference(parameterValue, parameterName);

			if (typeof(T) != parameterValue.GetType())
			{
				throw new ArgumentException(string.Format("Value is not of correct type, expected: {0} actual: {1}", typeof(T).Name, parameterValue.GetType().Name), parameterName);
			}

			return (T)parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <returns></returns>
		public static T CheckTypeAssignableToAndCast<T>(object parameterValue, string parameterName) where T : class
		{
			CheckForNullReference(parameterValue, parameterName);

			if (!(parameterValue is T))
			{
				throw new ArgumentException(string.Format("Value is not of correct type, expected: {0} actual: {1}", typeof(T).Name, parameterValue.GetType().Name), parameterName);
			}

			return (T) parameterValue;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="parameterValue"></param>
		/// <param name="parameterName"></param>
		/// <param name="ignoreCase"></param>
		/// <returns></returns>
		public static T CheckAndParseEnumeration<T>(string parameterValue, string parameterName, bool ignoreCase)
			where T : struct
		{
			CheckForNullReference(parameterValue, parameterName);

			Type enumType = typeof(T);
			try
			{
				return (T)Enum.Parse(enumType, parameterValue, ignoreCase);
			}
			catch (ArgumentException exception)
			{
				string exceptionMessage = string.Format("Enumeration [{0}] is not defined for [{1}] parameter [{2}], value [{3}]", enumType.FullName,
					ignoreCase ? "case-insensitive" : "case-sensitive", parameterName, parameterValue);
				throw new ArgumentException(exceptionMessage, exception);
			}
		}

		public static void CheckParameterBelongsToList(string parameterValue, string parameterName, List<string> expectedList )
		{
			CheckForNullReference(parameterValue, parameterName);
			CheckForNullReference(expectedList, "ExpectedParameterList");
			if (!expectedList.Contains(parameterValue, StringComparer.OrdinalIgnoreCase))
			{
				throw new ArgumentException(string.Format("Parameter value '{0}' does not belong to the expected list of parameters for Parameter name '{1}'", parameterValue, parameterName));
			}
		}

		private static void CheckParameterName(string parameterName)
		{
			if (string.IsNullOrEmpty(parameterName))
			{
				throw new ArgumentException("Value cannot be null or empty.", "parameterName");
			}
		}
	}
}



CREATE PROCEDURE [dbo].[generate_audit_trigger] @TableName VARCHAR(250)
AS	
BEGIN	

DECLARE @TriggerName AS VARCHAR(106)
SET @TriggerName = 'tr_' + @TableName + '_audit'
DECLARE @Date AS VARCHAR(11)
SET @Date = CONVERT(VARCHAR(11),GETUTCDATE(),106)

DECLARE @HasCheckSum AS BIT = 0

DECLARE @Schema VARCHAR(100)
DECLARE @NextCol VARCHAR(100)

SELECT @Schema = s.name FROM sys.schemas s JOIN sys.objects o
		ON o.schema_id = s.schema_id and o.name = @TableName

IF @@ROWCOUNT = 0 BEGIN
	PRINT 'Cannot Find Table ' + @TableName
	RETURN
END 

PRINT '--** Object:  Trigger '+ @TriggerName + ' Generated by generate_audit_trigger for user ' + USER_NAME() + ' Script Date: ' + @Date
PRINT 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = ''' + @TriggerName + ''')
		DROP TRIGGER ' + @Schema + '.' + @TriggerName
PRINT ''

PRINT 'GO'
PRINT '/* *********************************************************************************
**
**   Name:			' + @TriggerName
PRINT '**   Description:  Update/Delete trigger creation script for ' + @TableName
PRINT '**
**
**********************************************************************************
**                     CHANGE HISTORY
**  Change No:   Date:          Author:					Description:
**  _________    ___________    ______    ______________________________________
**     001      '+@Date+'     generate_audit_trigger	Generated.                             
** ****************************************************************************** */'
END
							


PRINT 'CREATE TRIGGER ' + @TriggerName + ' ON '+@Schema+'.' + @TableName
PRINT 'AFTER UPDATE,DELETE AS
BEGIN 
SET NOCOUNT ON 							--to avoid the rowcount messages
SET ROWCOUNT 0 							--in case the client has modified the rowcount

DECLARE @UserName VARCHAR(100)
DECLARE @StartTime DATETIME
DECLARE @audit_action CHAR(1)

IF EXISTS (SELECT * FROM Inserted)
BEGIN
	IF EXISTS (SELECT * FROM Deleted)
		SELECT @audit_action =''U''
	ELSE
		SELECT @audit_action =''I''
END
ELSE
	SELECT @audit_action =''D''


BEGIN TRY

	SET @UserName = SYSTEM_USER
	SET @StartTime = GETDATE()

	INSERT INTO ' + 'audit' + '.audit_' + @TableName


-- Now we need to build up our columnlist for the table
DECLARE q1 CURSOR FOR SELECT name FROM sys.columns WHERE object_name(object_id) = @TableName ORDER BY column_id
OPEN q1
FETCH q1 INTO @NextCol
PRINT '		(' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH q1 INTO @NextCol
	IF @NextCol = 'CheckSumValue' SET @HasCheckSum = 1
	IF @@FETCH_STATUS = 0 PRINT '		,' + @NextCol
END
PRINT '		,audit_action)'
CLOSE q1
OPEN q1
FETCH q1 INTO @NextCol
PRINT '	SELECT d.' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH q1 INTO @NextCol
	IF @@FETCH_STATUS = 0 PRINT '		,d.' + @NextCol
END
PRINT '		,audit_action = @audit_action
	FROM Deleted d'
CLOSE q1
DEALLOCATE q1
DECLARE q1 CURSOR FOR SELECT c.name FROM sys.columns c
			JOIN sys.index_columns ic ON c.column_id = ic.column_id AND c.object_id = ic.object_id
			JOIN sys.indexes i ON ic.index_id = i.index_id AND ic.object_id = i.object_id
WHERE i.is_primary_key = 1 AND Object_name(i.object_id) = @TableName
ORDER BY index_column_id

OPEN q1
FETCH q1 INTO @NextCol
PRINT '	LEFT JOIN Inserted i ON i.' + @NextCol + ' = d.' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH q1 INTO @NextCol
	IF @@FETCH_STATUS = 0 PRINT '				AND		i.' + @NextCol + ' = d.'+@NextCol
END
CLOSE q1
DEALLOCATE q1
IF @HasCheckSum = 1 PRINT '	WHERE ISNULL(i.checkSumValue,-1) <> d.CheckSumValue'

PRINT '
END TRY

BEGIN CATCH

    DECLARE @ErrorMessage   NVARCHAR(4000)
    DECLARE @ErrorNumber    INT
    DECLARE @ErrorSeverity  INT
    DECLARE @ErrorState     INT
    DECLARE @ErrorLine      INT

    SELECT @ErrorMessage   = ERROR_MESSAGE() 
          ,@ErrorNumber    = ERROR_NUMBER()
          ,@ErrorSeverity  = ERROR_SEVERITY()
          ,@ErrorState     = ERROR_STATE()
          ,@ErrorLine      = ERROR_LINE()


   RAISERROR (@ErrorMessage,
            @ErrorSeverity, 
            @ErrorState 
            )

END CATCH
END
GO
'

CREATE PROCEDURE [dbo].[generate_audit_table] @TableName VARCHAR(250)
AS
BEGIN


SET NOCOUNT ON
DECLARE @String varchar(8000), @String2  varchar(8000), @Schema VARCHAR(100)
-- Assign table name to output
SET @String2 = ''

SELECT @Schema = s.name FROM sys.schemas s JOIN sys.objects o
		ON o.schema_id = s.schema_id and o.name = @TableName

IF @@ROWCOUNT = 0 BEGIN
	PRINT 'Cannot Find Table ' + @TableName
	RETURN
END 

PRINT 'IF EXISTS (SELECT 1 FROM sys.tables WHERE type = ''U'' AND name = ''audit_' + @TableName + ''' AND schema_name(schema_id) = ''' + 'audit' +''')'
PRINT '	DROP TABLE ' + 'audit' + '.audit_' + @TableName
PRINT 'GO'

DECLARE @Date AS VARCHAR(11)
SET @Date = CONVERT(VARCHAR(11),GETDATE(),106)


PRINT '/* *********************************************************************************
**
**   Name:			' + 'audit' + '.audit_' + @TableName
PRINT '**   Description:  Audit Table Creation Script script for ' + @Schema + '.zAudit' + @TableName
PRINT '**
**
**********************************************************************************
**                     CHANGE HISTORY
**  Change No:   Date:          Author:					Description:
**  _________    ___________    ______    ______________________________________
**     001      '+@Date+'     generate_audit_table	Generated.                             
** ****************************************************************************** */'


SET @String = 'CREATE TABLE ' + 'audit' + '.audit_' + @TableName + '(audit_id int identity,'


SELECT @String2 = @String2 + 
sys.columns.name+' '+sys.types.name
	+ CASE 
		-- Need Precision and Scale for decimal and numeric
		WHEN sys.columns.system_type_id IN (106, 108) THEN '('+CONVERT(varchar,sys.columns.precision)+', '+CONVERT(varchar,sys.columns.scale)+')'
		-- Need Size for varchar, char, nvarchar, and nchar 
		WHEN sys.columns.system_type_id IN (167, 175, 231, 239,165) THEN CASE WHEN sys.columns.max_length = -1 THEN '(max)' ELSE '('+CONVERT(varchar,sys.columns.max_length)+')' END
		-- Do not need more for image, text, uniqueidentifier, tinyint, smallint, int, smalldatetime, real, money, datetime, float, sql_variant, ntext, bit, smallmoney, bigint, varbinary, binary, timestamp
		--	(34, 35, 36, 48, 52, 56, 58, 59, 60, 61, 62, 98, 99, 104, 122, 127, 165, 173, 189)		
		ELSE '' 
	END + ','  + CHAR(10)
FROM sys.columns 
	INNER JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id 
	INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE sys.objects.name = @TableName
ORDER BY sys.columns.column_id

SET @String2 = @String2 + 'audit_user VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER,' + CHAR(10)
SET @String2 = @String2 + 'audit_date_time DATETIME2 NOT NULL DEFAULT GETDATE(),' + CHAR(10)
SET @String2 = @String2 + 'audit_action CHAR(1) NOT NULL)'

PRINT @String + @String2
PRINT 'GO'


END

Model

​x
 
using System;
using System.Collections.Generic;
using System.Linq;
​
namespace abc.ArgumentValidation
{
    /// <summary>
    /// 
    /// </summary>
    public static class ArgumentValidator
    {
        internal sealed class ValidatedNotNullAttribute : Attribute
        {}
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static T CheckForNullReference<T>([ValidatedNotNull][NoEnumeration] T parameterValue, string parameterName) where T : class
        {
            CheckParameterName(parameterName);
​
            if (parameterValue == null)
            {
                throw new ArgumentNullException(parameterName);
            }
​
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public static T CheckForNullReference<T>([ValidatedNotNull][NoEnumeration] T parameterValue, string parameterName, string message) where T : class
        {
            CheckParameterName(parameterName);
​
            if (parameterValue == null)
            {
                throw new ArgumentNullException(parameterName, message);
            }
​
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static T CheckForNullReference<T>(T? parameterValue, string parameterName) where T : struct
        {
            CheckParameterName(parameterName);
​
            if (!parameterValue.HasValue)
            {
                throw new ArgumentNullException(parameterName);
            }
​
            return parameterValue.Value;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static string CheckForNullOrEmptyString([ValidatedNotNull] string parameterValue, string parameterName)
        {
            return CheckForNullOrEmptyString(parameterValue, parameterName, "Value cannot be null or empty.");
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public static string CheckForNullOrEmptyString([ValidatedNotNull] string parameterValue, string parameterName, string message)
        {
            CheckParameterName(parameterName);
​
            if (string.IsNullOrEmpty(parameterValue))
            {
                throw new ArgumentException(message, parameterName);
            }
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <param name="throwIf"></param>
        /// <param name="parameterName"></param>
        /// <param name="message"></param>
        public static void ConditionalArgumentException(bool throwIf, string parameterName, string message)
        {
            CheckParameterName(parameterName);
​
            if (throwIf)
            {
                throw new ArgumentException(message, parameterName);
            }
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static T[] CheckArrayNotNullOrEmpty<T>([ValidatedNotNull] T[] parameterValue, string parameterName)
        {
            CheckForNullReference(parameterValue, parameterName);
​
            if (parameterValue.Length == 0)
            {
                throw new ArgumentException("Array cannot be empty.", parameterName);
            }
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <param name="minSize"></param>
        /// <returns></returns>
        public static T[] CheckArrayAboveMinimumLength<T>([ValidatedNotNull] T[] parameterValue, string parameterName, int minSize)
        {
            CheckForNullReference(parameterValue, parameterName);
​
            if (parameterValue.Length < minSize)
            {
                throw new ArgumentException(string.Format("Array was expected to contain at least [{0}] element(s) but actually contained [{1}]", minSize, parameterValue.Length), parameterName);
            }
​
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public static T[] CheckArrayLength<T>([ValidatedNotNull] T[] parameterValue, string parameterName, int length)
        {
            CheckForNullReference(parameterValue, parameterName);
​
            if (parameterValue.Length != length)
            {
                throw new ArgumentException(string.Format("Array was expected to contain [{0}] element(s) but actually contained [{1}]", length, parameterValue.Length), parameterName);
            }
​
            return parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static T CheckTypeCorrectAndCast<T>(object parameterValue, string parameterName) where T : class
        {
            CheckForNullReference(parameterValue, parameterName);
​
            if (typeof(T) != parameterValue.GetType())
            {
                throw new ArgumentException(string.Format("Value is not of correct type, expected: {0} actual: {1}", typeof(T).Name, parameterValue.GetType().Name), parameterName);
            }
​
            return (T)parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static T CheckTypeAssignableToAndCast<T>(object parameterValue, string parameterName) where T : class
        {
            CheckForNullReference(parameterValue, parameterName);
​
            if (!(parameterValue is T))
            {
                throw new ArgumentException(string.Format("Value is not of correct type, expected: {0} actual: {1}", typeof(T).Name, parameterValue.GetType().Name), parameterName);
            }
​
            return (T) parameterValue;
        }
​
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterValue"></param>
        /// <param name="parameterName"></param>
        /// <param name="ignoreCase"></param>
        /// <returns></returns>
        public static T CheckAndParseEnumeration<T>(string parameterValue, string parameterName, bool ignoreCase)
            where T : struct
        {
            CheckForNullReference(parameterValue, parameterName);
​
            Type enumType = typeof(T);
            try
            {
                return (T)Enum.Parse(enumType, parameterValue, ignoreCase);
            }
            catch (ArgumentException exception)
            {
                string exceptionMessage = string.Format("Enumeration [{0}] is not defined for [{1}] parameter [{2}], value [{3}]", enumType.FullName,
                    ignoreCase ? "case-insensitive" : "case-sensitive", parameterName, parameterValue);
                throw new ArgumentException(exceptionMessage, exception);
            }
        }
​
        public static void CheckParameterBelongsToList(string parameterValue, string parameterName, List<string> expectedList )
        {
            CheckForNullReference(parameterValue, parameterName);
            CheckForNullReference(expectedList, "ExpectedParameterList");
            if (!expectedList.Contains(parameterValue, StringComparer.OrdinalIgnoreCase))
            {
                throw new ArgumentException(string.Format("Parameter value '{0}' does not belong to the expected list of parameters for Parameter name '{1}'", parameterValue, parameterName));
            }
        }
​
        private static void CheckParameterName(string parameterName)
        {
            if (string.IsNullOrEmpty(parameterName))
            {
                throw new ArgumentException("Value cannot be null or empty.", "parameterName");
            }
        }
    }
}
​
​
​
CREATE PROCEDURE [dbo].[generate_audit_trigger] @TableName VARCHAR(250)
AS  
BEGIN   
​
DECLARE @TriggerName AS VARCHAR(106)
SET @TriggerName = 'tr_' + @TableName + '_audit'
DECLARE @Date AS VARCHAR(11)
SET @Date = CONVERT(VARCHAR(11),GETUTCDATE(),106)
​
DECLARE @HasCheckSum AS BIT = 0
​
DECLARE @Schema VARCHAR(100)
DECLARE @NextCol VARCHAR(100)
​
SELECT @Schema = s.name FROM sys.schemas s JOIN sys.objects o
        ON o.schema_id = s.schema_id and o.name = @TableName
​
IF @@ROWCOUNT = 0 BEGIN
    PRINT 'Cannot Find Table ' + @TableName
    RETURN
END 
​
PRINT '--** Object:  Trigger '+ @TriggerName + ' Generated by generate_audit_trigger for user ' + USER_NAME() + ' Script Date: ' + @Date
PRINT 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = ''' + @TriggerName + ''')
        DROP TRIGGER ' + @Schema + '.' + @TriggerName
PRINT ''
​
PRINT 'GO'
PRINT '/* *********************************************************************************
**
**   Name:          ' + @TriggerName
PRINT '**   Description:  Update/Delete trigger creation script for ' + @TableName
PRINT '**
**
**********************************************************************************
**                     CHANGE HISTORY
**  Change No:   Date:          Author:                 Description:
**  _________    ___________    ______    ______________________________________
**     001      '+@Date+'     generate_audit_trigger    Generated.                             
** ****************************************************************************** */'
END
                            
​
​
PRINT 'CREATE TRIGGER ' + @TriggerName + ' ON '+@Schema+'.' + @TableName
PRINT 'AFTER UPDATE,DELETE AS
BEGIN 
SET NOCOUNT ON                          --to avoid the rowcount messages
SET ROWCOUNT 0                          --in case the client has modified the rowcount
​
DECLARE @UserName VARCHAR(100)
DECLARE @StartTime DATETIME
DECLARE @audit_action CHAR(1)
​
IF EXISTS (SELECT * FROM Inserted)
BEGIN
    IF EXISTS (SELECT * FROM Deleted)
        SELECT @audit_action =''U''
    ELSE
        SELECT @audit_action =''I''
END
ELSE
    SELECT @audit_action =''D''
​
​
BEGIN TRY
​
    SET @UserName = SYSTEM_USER
    SET @StartTime = GETDATE()
​
    INSERT INTO ' + 'audit' + '.audit_' + @TableName
​
​
-- Now we need to build up our columnlist for the table
DECLARE q1 CURSOR FOR SELECT name FROM sys.columns WHERE object_name(object_id) = @TableName ORDER BY column_id
OPEN q1
FETCH q1 INTO @NextCol
PRINT '     (' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH q1 INTO @NextCol
    IF @NextCol = 'CheckSumValue' SET @HasCheckSum = 1
    IF @@FETCH_STATUS = 0 PRINT '       ,' + @NextCol
END
PRINT '     ,audit_action)'
CLOSE q1
OPEN q1
FETCH q1 INTO @NextCol
PRINT ' SELECT d.' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH q1 INTO @NextCol
    IF @@FETCH_STATUS = 0 PRINT '       ,d.' + @NextCol
END
PRINT '     ,audit_action = @audit_action
    FROM Deleted d'
CLOSE q1
DEALLOCATE q1
DECLARE q1 CURSOR FOR SELECT c.name FROM sys.columns c
            JOIN sys.index_columns ic ON c.column_id = ic.column_id AND c.object_id = ic.object_id
            JOIN sys.indexes i ON ic.index_id = i.index_id AND ic.object_id = i.object_id
WHERE i.is_primary_key = 1 AND Object_name(i.object_id) = @TableName
ORDER BY index_column_id
​
OPEN q1
FETCH q1 INTO @NextCol
PRINT ' LEFT JOIN Inserted i ON i.' + @NextCol + ' = d.' + @NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH q1 INTO @NextCol
    IF @@FETCH_STATUS = 0 PRINT '               AND     i.' + @NextCol + ' = d.'+@NextCol
END
CLOSE q1
DEALLOCATE q1
IF @HasCheckSum = 1 PRINT ' WHERE ISNULL(i.checkSumValue,-1) <> d.CheckSumValue'
​
PRINT '
END TRY
​
BEGIN CATCH
​
    DECLARE @ErrorMessage   NVARCHAR(4000)
    DECLARE @ErrorNumber    INT
    DECLARE @ErrorSeverity  INT
    DECLARE @ErrorState     INT
    DECLARE @ErrorLine      INT
​
    SELECT @ErrorMessage   = ERROR_MESSAGE() 
          ,@ErrorNumber    = ERROR_NUMBER()
          ,@ErrorSeverity  = ERROR_SEVERITY()
          ,@ErrorState     = ERROR_STATE()
          ,@ErrorLine      = ERROR_LINE()
​
​
   RAISERROR (@ErrorMessage,
            @ErrorSeverity, 
            @ErrorState 
            )
​
END CATCH
END
GO
'
​
CREATE PROCEDURE [dbo].[generate_audit_table] @TableName VARCHAR(250)
AS
BEGIN
​
​
SET NOCOUNT ON
DECLARE @String varchar(8000), @String2  varchar(8000), @Schema VARCHAR(100)
-- Assign table name to output
SET @String2 = ''
​
SELECT @Schema = s.name FROM sys.schemas s JOIN sys.objects o
        ON o.schema_id = s.schema_id and o.name = @TableName
​
IF @@ROWCOUNT = 0 BEGIN
    PRINT 'Cannot Find Table ' + @TableName
    RETURN
END 
​
PRINT 'IF EXISTS (SELECT 1 FROM sys.tables WHERE type = ''U'' AND name = ''audit_' + @TableName + ''' AND schema_name(schema_id) = ''' + 'audit' +''')'
PRINT ' DROP TABLE ' + 'audit' + '.audit_' + @TableName
PRINT 'GO'
​
DECLARE @Date AS VARCHAR(11)
SET @Date = CONVERT(VARCHAR(11),GETDATE(),106)
​
​
PRINT '/* *********************************************************************************
**
**   Name:          ' + 'audit' + '.audit_' + @TableName
PRINT '**   Description:  Audit Table Creation Script script for ' + @Schema + '.zAudit' + @TableName
PRINT '**
**
**********************************************************************************
**                     CHANGE HISTORY
**  Change No:   Date:          Author:                 Description:
**  _________    ___________    ______    ______________________________________
**     001      '+@Date+'     generate_audit_table  Generated.                             
** ****************************************************************************** */'
​
​
SET @String = 'CREATE TABLE ' + 'audit' + '.audit_' + @TableName + '(audit_id int identity,'
​
​
SELECT @String2 = @String2 + 
sys.columns.name+' '+sys.types.name
    + CASE 
        -- Need Precision and Scale for decimal and numeric
        WHEN sys.columns.system_type_id IN (106, 108) THEN '('+CONVERT(varchar,sys.columns.precision)+', '+CONVERT(varchar,sys.columns.scale)+')'
        -- Need Size for varchar, char, nvarchar, and nchar 
        WHEN sys.columns.system_type_id IN (167, 175, 231, 239,165) THEN CASE WHEN sys.columns.max_length = -1 THEN '(max)' ELSE '('+CONVERT(varchar,sys.columns.max_length)+')' END
        -- Do not need more for image, text, uniqueidentifier, tinyint, smallint, int, smalldatetime, real, money, datetime, float, sql_variant, ntext, bit, smallmoney, bigint, varbinary, binary, timestamp
        --  (34, 35, 36, 48, 52, 56, 58, 59, 60, 61, 62, 98, 99, 104, 122, 127, 165, 173, 189)      
        ELSE '' 
    END + ','  + CHAR(10)
FROM sys.columns 
    INNER JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id 
    INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE sys.objects.name = @TableName
ORDER BY sys.columns.column_id
​
SET @String2 = @String2 + 'audit_user VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER,' + CHAR(10)
SET @String2 = @String2 + 'audit_date_time DATETIME2 NOT NULL DEFAULT GETDATE(),' + CHAR(10)
SET @String2 = @String2 + 'audit_action CHAR(1) NOT NULL)'
​
PRINT @String + @String2
PRINT 'GO'
​
​
END
​
​
​

View

​

Controller

​

View in Full Screen

The sum of x and y is: 8

Last Run:	1:19:23 am
Compile:	0.006s
Execute:	0.05s
Memory:	2.97Mb
CPU:	0.056s

View IL Code