-- Wrap the process in an error handler so we can bail out 
-- if something goes wrong
BEGIN TRY
	SET NOCOUNT ON;
	BEGIN TRANSACTION;
	DECLARE @TABLE_CATALOG sysname, 
			@TABLE_SCHEMA sysname, 
			@TABLE_NAME sysname,
			@OBJECT_NAME sysname,
			@NEW_NAME sysname,
			@GRANTEE sysname, 
			@DOMAIN_NAME sysname,
			@COLUMN_NAMES varchar(MAX), 
			@SQL varchar(MAX);
	SELECT @DOMAIN_NAME = 'MYDOMAIN\', @GRANTEE = 'MyRoleGroup',
		   @COLUMN_NAMES = '', @SQL = '';
	DECLARE myCursor00 CURSOR FOR
		SELECT  DB_NAME(), TABLE_SCHEMA, TABLE_NAME
		FROM dbo.tblAudit
	;
	OPEN myCursor00;
	FETCH NEXT FROM myCursor00 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME
		FROM INFORMATION_SCHEMA.Columns
		WHERE TABLE_CATALOG = @TABLE_CATALOG 
		  AND TABLE_SCHEMA = @TABLE_SCHEMA 
		  AND TABLE_NAME = @TABLE_NAME
		ORDER BY ORDINAL_POSITION;
		SELECT @SQL = 'IF OBJECT_ID(''' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_SCHEMA + '_' +  @TABLE_NAME + '_TR;';
		PRINT @SQL; 
		EXEC(@SQL);
		SELECT @SQL = '';
		FETCH NEXT FROM myCursor00 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME; 
	END;
	CLOSE myCursor00;
	DEALLOCATE myCursor00;

	DECLARE myCursor01 CURSOR FOR
		SELECT  DB_NAME(), TABLE_SCHEMA, TABLE_NAME
		FROM dbo.tblAudit
	;
	SELECT @SQL = '', @COLUMN_NAMES = '';

	OPEN myCursor01;
	FETCH NEXT FROM myCursor01 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
	WHILE @@FETCH_STATUS = 0
		BEGIN
		IF OBJECT_ID(@TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H') IS NOT NULL
		BEGIN
			SELECT
				@OBJECT_NAME = @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H',
				@NEW_NAME = '#' + @TABLE_NAME + '_H'		
			;
			SELECT @SQL = ']CRLF[ SELECT * INTO ' + @NEW_NAME + ' FROM ' + @OBJECT_NAME + ';]CRLF[ DROP TABLE ' + @OBJECT_NAME + ';';
			PRINT 'RENAMED ' + @OBJECT_NAME + ' TO ' + @NEW_NAME; 
		END;
		SELECT @SQL = @SQL + ']CRLF[ CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' +  @TABLE_NAME + '_H ( ]CRLF['
					+ '  HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL ]CRLF['
					+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL ]CRLF['
					+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) ]CRLF[';

		SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME + ' ' + DATA_TYPE
				+ CASE  WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
						WHEN DATA_TYPE = 'decimal'    THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
							+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
						ELSE ''
					END
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_CATALOG = @TABLE_CATALOG 
		  AND TABLE_SCHEMA = @TABLE_SCHEMA 
		  AND TABLE_NAME = @TABLE_NAME
		ORDER BY ORDINAL_POSITION;

		SELECT @SQL = @SQL + @COLUMN_NAMES + ');]CRLF[';
	
		IF LEN(@NEW_NAME) > 0
		BEGIN
			SELECT @SQL = @SQL + 'DECLARE @COLUMN_NAMES varchar(MAX), @SQL varchar(MAX); ]CRLF['
							   + 'SELECT @COLUMN_NAMES = '''', @SQL = '''';]CRLF['
							   + 'SELECT @COLUMN_NAMES = @COLUMN_NAMES + '', '' +  n.COLUMN_NAME ]CRLF['
							   + 'FROM ( ]CRLF[ '
							   + '	SELECT COLUMN_NAME, ORDINAL_POSITION ]CRLF['
							   + '  FROM INFORMATION_SCHEMA.COLUMNS ]CRLF['
							   + '  WHERE TABLE_CATALOG = ''' + @TABLE_CATALOG + ''' ]CRLF['
							   + '    AND TABLE_SCHEMA = ''' + @TABLE_SCHEMA + ''' ]CRLF['
							   + '    AND TABLE_NAME = ''' + @TABLE_NAME + '_H'' ]CRLF['
							   + '    AND DATA_TYPE <> ''timestamp'' ]CRLF['
							   + '    AND DATA_TYPE <> ''rowversion'' ]CRLF['
							   + ') AS n ]CRLF['
							   + 'INNER JOIN ( ]CRLF['
							   + 'SELECT COLUMN_NAME ]CRLF['
							   + 'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ]CRLF['
							   + 'WHERE TABLE_CATALOG = ''tempdb'' ]CRLF['
							   + '    AND TABLE_SCHEMA = ''' + @TABLE_SCHEMA + ''' ]CRLF[' 
							   + '    AND TABLE_NAME LIKE ''#' + @TABLE_NAME + '_H%'' ]CRLF['
							   + ') AS o ]CRLF['
							   + 'ON n.COLUMN_NAME = o.COLUMN_NAME ]CRLF['
							   + 'ORDER BY n.ORDINAL_POSITION; ]CRLF['
							   + 'IF (LEN(@COLUMN_NAMES) > 0) ]CRLF['
							   + 'BEGIN '
							   + '    SELECT @SQL = @SQL +  '' INSERT INTO ' + @OBJECT_NAME + ' ( '' '
							   + '			             +  ''    ]['' + @COLUMN_NAMES + '' '' '
							   + '                       + '') SELECT ]['' + @COLUMN_NAMES + '' '' '
							   + '                       + ''FROM ' + @NEW_NAME + ';'''
							   + '						 + ''DROP TABLE ' + @NEW_NAME + ';'''
							   + '    SELECT @SQL = REPLACE(@SQL, ''][,'', '''');'
							   + '    PRINT @SQL;'
							   + '    EXEC (@SQL);'
							   + 'END;'		
		END;	
		SELECT @SQL = REPLACE(@SQL,']CRLF[',CHAR(13) + CHAR(10));
		SELECT @SQL = REPLACE(@SQL, '-1', 'MAX');
		SELECT @SQL = REPLACE(@SQL, ', Default ', ', [Default] ');
		PRINT @SQL;
		EXEC(@SQL);

		SELECT @SQL = '', @COLUMN_NAMES = '', @OBJECT_NAME = '', @NEW_NAME = '';
		FETCH NEXT FROM myCursor01 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
	END;
	CLOSE myCursor01;
	DEALLOCATE myCursor01;

	DECLARE myCursor02 CURSOR FOR
		SELECT DB_NAME(), TABLE_SCHEMA, TABLE_NAME
		FROM dbo.tblAudit
	;
	SELECT @COLUMN_NAMES = '';
	OPEN myCursor02;
	FETCH NEXT FROM myCursor02 INTO @TABLE_CATALOG, @TABLE_SCHEMA,@TABLE_NAME;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME + '_H TO ' + @GRANTEE + ';';
		PRINT @SQL;
		EXEC(@SQL);
		SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + '_H TO ' + @GRANTEE + ';';
		EXEC(@SQL);
		FETCH NEXT FROM myCursor02 INTO @TABLE_CATALOG,@TABLE_SCHEMA, @TABLE_NAME;
	END;
	CLOSE myCursor02;
	DEALLOCATE myCursor02;

	DECLARE myCursor03 CURSOR FOR
		SELECT  DB_NAME(), TABLE_SCHEMA, TABLE_NAME 
		FROM dbo.tblAudit
	;
	SELECT @COLUMN_NAMES = '';
	OPEN myCursor03;
	FETCH NEXT FROM myCursor03 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
		FROM INFORMATION_SCHEMA.Columns
		WHERE TABLE_CATALOG = @TABLE_CATALOG 
		  AND TABLE_SCHEMA = @TABLE_SCHEMA 
		  AND TABLE_NAME = @TABLE_NAME 
		  AND (DATA_TYPE <> 'timestamp' AND DATA_TYPE <> 'rowversion')
		ORDER BY ORDINAL_POSITION;

		SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ]CRLF['
					+ 'ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME + ' FOR UPDATE, DELETE AS ]CRLF['
					+ 'BEGIN ]CRLF[' 
					+ '    DECLARE @HOSTNAME sysname, @DESC varchar(50); ]CRLF['
					+ '    SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID; ]CRLF['
					+ '    IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') ]CRLF['
					+ '        SELECT @DESC = ' + '''' + '''' + ';]CRLF['
					+ '    ELSE ]CRLF['
					+ '        SELECT @DESC = ' + '''' + 'MASS DELETE' + ''';]CRLF['
					+ '    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) ]CRLF['
					+ '        INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF[' 
					+ '            HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ') ]CRLF['
					+ '	       SELECT ''U'', SYSTEM_USER, REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''), @HOSTNAME, @@SPID, @DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ']CRLF['
					+ '        FROM deleted;]CRLF['
					+ '    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) AND @DESC = '''' ]CRLF['
					+ '        INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF['
					+ '            HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ') ]CRLF['
					+ '        SELECT ''D'', SYSTEM_USER, REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''), @HOSTNAME, @@SPID, @DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ']CRLF['
					+ '        FROM deleted;]CRLF['
					+ '    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) And @DESC <> '''' ]CRLF['
					+ '        INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF['
					+ '            HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ') ]CRLF['
					+ '        SELECT TOP 1 ''D'', SYSTEM_USER, REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''), @HOSTNAME, @@SPID, @DESC ]CRLF['
					+ '            ' + @COLUMN_NAMES + ']CRLF['
					+ '        FROM deleted;]CRLF['
					+ 'END;'
		;
		SELECT @SQL = REPLACE(@SQL, ']CRLF[',CHAR(13) + CHAR(10));
		SELECT @SQL = REPLACE(@SQL, ', Default ', ', [Default] ');
		SELECT @SQL = REPLACE(@SQL, ', Default) ', ', [Default]) ');
		PRINT @SQL;
		EXEC(@SQL);
		SELECT @SQL = '', @COLUMN_NAMES = '';
		FETCH NEXT FROM myCursor03 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
	END;
	CLOSE myCursor03;
	DEALLOCATE myCursor03;
	COMMIT;
END TRY BEGIN CATCH
	PRINT ERROR_NUMBER();
	PRINT ERROR_MESSAGE();
	PRINT ERROR_LINE();
	IF (@@TRANCOUNT > 0)
		ROLLBACK;
	IF CURSOR_STATUS('global','myCursor00') = 1
		CLOSE myCursor00;
	IF CURSOR_STATUS('global','myCursor00') = -1
		DEALLOCATE myCursor00;
	IF CURSOR_STATUS('global','myCursor01') = 1
		CLOSE myCursor01;
	IF CURSOR_STATUS('global','myCursor01') = -1
		DEALLOCATE myCursor01;
	IF CURSOR_STATUS('global','myCursor02') = 1
		CLOSE myCursor02;
	IF CURSOR_STATUS('global','myCursor02') = -1
		DEALLOCATE myCursor02;
	IF CURSOR_STATUS('global','myCursor03') = 1
		CLOSE myCursor03;
	IF CURSOR_STATUS('global','myCursor03') = -1
		DEALLOCATE myCursor03;
END CATCH;