Mickey's T-SQL Ponderings

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

The Final Piece

The last couple of days I’ve written here and here about how to add simple auditing to your tables using ER Studio Data Architect. Today is the finishing piece. Today I will share my SQL Prompt script that creates the triggers for the tables with the ModifiedBy and ModifiedDTS fields.

The trigger is an update trigger. Whenever the record is change, the trigger will be executed. If the user provided a value for the ModifieidBy field, then that value will be used. If a new value is not provided, then the SUSER_NAME() function will be used. I put that check in there for applications that use a single credential to log in to SQL Server, but the application can send in the actually user. If you don’t need that feature, then it can be taken out.

Before running the code, do the following.

1. Use CTRL+SHIFT+M to update the script with the values you need. This will include a comma delimited list of table names, and a single schema name. (Eventually, I’ll add functionality for multiple schemas.)
2. The script is setup to print the triggers to the Messages pane. You can copy the scripts into another window to execute, or you can set the @ReadyToExecute bit to 1. This will cause the scripts to be executed as the triggers are built.

USE 
GO
/* ***********************************************************************************
Purpose:    Creates new update triggers for tables that use ModifiedDTS and ModifiedBy.
Notes:      1. Use Shift + Ctrl + M to set Template Parameters
			2. Change the @ReadyToexecute bit to 1, when you are ready to execute the
				code, or simply run the sqcript that is generated in the Messages pane.
WARNING: 	Make sure you verify that a trigger with the same name as the one being 
		generated  does not exists. IT WILL BE DROPPED with this code. ALSO, make 
		sure adding this trigger, will not cause issues with other existing 
		update trigers.
Author:     Mickey Stuewe, www.mickeystuewe.com
Date:       12/10/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
DECLARE 
	@ReadyToExecute AS bit = 1

DECLARE
	 @Tables AS nvarchar(max) = ''
	,@SchemaName AS nvarchar(50) = '<schema,string,>'
	,@TriggerName AS nvarchar(200)
	,@TableName AS nvarchar(126)
	,@SQL AS nvarchar(MAX)
	,@PrimaryKeys_t_to_d AS nvarchar(MAX)
	,@PrimaryKeys_d_to_i AS nvarchar(MAX)

	DECLARE Trig_Cursor CURSOR	
		FOR 
			WITH cte_Pieces
			AS 
			(
				SELECT
					 1 AS ID
					,1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables)) AS StopString
				UNION ALL
				SELECT
					ID + 1 AS ID
					,StopString + 1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables, StopString + 1)) AS StopString
				FROM
					cte_Pieces
				WHERE
					StopString > 0
			)
			,cte_Tables
			AS
			(
				SELECT
					SUBSTRING(@Tables, StartString, CASE WHEN StopString > 0 THEN StopString - StartString
																ELSE LEN(@Tables)
														   END) AS TableName
				FROM
					cte_Pieces
			)
			SELECT 
				TableName
				,'trg_' + TableName + '_u' AS TriggerName
			FROM 
				cte_Tables

	OPEN Trig_Cursor
	FETCH NEXT FROM Trig_Cursor
	INTO 
		 @TableName
		,@TriggerName
	WHILE @@FETCH_STATUS = 0
	BEGIN

		--Get all the primary keys
		SET @PrimaryKeys_t_to_d = NULL
		SET @PrimaryKeys_d_to_i = NULL

		SELECT
			@PrimaryKeys_t_to_d = ISNULL(@PrimaryKeys_t_to_d  + SPACE(36) + 'AND ','') +  't.' + KCU.COLUMN_NAME + ' = d.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 
			,@PrimaryKeys_d_to_i = ISNULL(@PrimaryKeys_d_to_i + SPACE(36) + 'AND ','') +  'd.' + KCU.COLUMN_NAME + ' = i.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 

		FROM
			INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
			JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
																				   AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
																				   AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		WHERE
			tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
			AND KCU.TABLE_NAME =  @TableName
			AND KCU.CONSTRAINT_SCHEMA = @SchemaName
		ORDER BY 
			KCU.ORDINAL_POSITION ASC

		--Drop existing Trigger
		SET @SQL = ''
		SET @SQL = @SQL +  N'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SchemaName + '].[' +  @TriggerName +  ']''))' + CHAR(13) + CHAR(10)
		SET @SQL = @SQL +  N'DROP TRIGGER ' + @SchemaName + '.' +  @TriggerName	+ CHAR(13) + CHAR(10)

		--PRINT @SQL
		EXECUTE sp_executesql @SQL

		--CREATE New Trigger
		SET @SQL =		  N''																						
		SET @SQL = @SQL + N'/* ********************************************************************************'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Template:	Created with template by Mickey Stuewe, www.mickeytsuewe.com'								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Purpose:	Set ModifiedDTS and ModifiedBy columns.'					 								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Notes:		ModifiedBy and ModifiedDTS will only changed if they were not changed'						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'			by the user.'																				+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10) 
		SET @SQL = @SQL + N'Author:		$USER$'  																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Date:		$DATE$' 																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Revision History'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'(Change Date)	(Author)		(Description of Change)'												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'-----------------------------------------------------------------'										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N' *********************************************************************************/'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'CREATE TRIGGER ' + @SchemaName + '.' +  @TriggerName +													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	ON ' + @SchemaName + '.' +  @TableName																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	AFTER UPDATE'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'AS '																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'BEGIN'																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT ON'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	-- Update the ModifiedDTS/By fields if they are not passed in. '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	UPDATE '																							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + 															+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		 ModifiedBy = CASE '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy IS NULL THEN SUSER_NAME() '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy <> d.ModifiedBy THEN i.ModifiedBy '							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN ISNULL(i.ModifiedDTS,d.ModifiedDTS) = d.ModifiedDTS THEN SUSER_NAME() '	+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						ELSE i.ModifiedBy '																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						END '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		,ModifiedDTS = GETDATE() '																		+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	FROM '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + ' AS t '													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		INNER JOIN deleted AS d ON ' + @PrimaryKeys_t_to_d												
		SET @SQL = @SQL + N'		INNER JOIN inserted AS i ON ' + @PrimaryKeys_d_to_i												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT OFF'																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'END '																									+ CHAR(13) + CHAR(10)

		IF @ReadyToExecute = 0
		BEGIN
			SET @SQL = @SQL + N'GO '																									+ CHAR(13) + CHAR(10)

			PRINT @SQL
		END
		ELSE
		BEGIN
			EXECUTE sp_executesql @SQL
		END

		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[' + @SchemaName + '].[' +  @TriggerName +  ']')) 
			PRINT '--CREATED TRIGGER ' + @SchemaName + '.' +  @TriggerName
		ELSE
			PRINT '--*** TRIGGER ' + @SchemaName + '.' +  @TriggerName + ' was not created.***'

		FETCH NEXT FROM Trig_Cursor
		INTO 
			@TableName
			,@TriggerName
	END
	CLOSE Trig_Cursor
	DEALLOCATE Trig_Cursor

GO

Here is an example of what the trigger will look like. This example has a three part primary key.

Previous Post In Series: SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)