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.
- 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.)
- 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.
| 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 triggers. |
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)
