SQL Advent Calendar 2013 – Day 8 – Creating Change Tracking Domains (1 of 3)

AdventCalendar08First 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.

First Step Is The Foundation

Whenever I need to have minimal auditing on a table, I add four fields to the table. InsertedBy, InsertedDTS, Modifiedby, and ModifiedDTS. The Inserted fields tell me who created the row. These two fields are updated based on the defaults set on the columns. The Modified fields tell me who changed the fields and are updated by a trigger.

Over the next couple of days I will show you two ER/Studio Data Architect (ER Studio DA) macros and one SQL Prompt Script that will help you create these four fields and the needed triggers.

The first step is making sure that your data model has four Domains. They reside in the Data Dictionary on the Data Dictionary tab. Domains are templates that fields can be created against.

There are two types of Data Dictionaries. There are local Data Dictionaries and Enterprise Data dictionaries. A data model can only have one local Data Dictionary and it always exists. If you are using ER Studio DA’s Repository feature, then you can create an Enterprise Data Dictionary to use across al Data Models. A Data Model can have multiple Enterprise Data Dictionaries. This is a great feature and I have used it in the past, but it isn’t available if you are not using the Repository.

 

AdventCalendar2013Day8_Img1

 

I’ve created a macro called Create Change Tracking Domains to use the local Data Dictionary.

The macro does the following: If a folder called Change Tracking doesn’t exist in the Data Dictionary, then it is created. If the four change tracking domains don’t exist, then they will be created. Either way, all the properties of these four domains will either be created or reset when this macro is run.

Note: You can modify it to use an Enterprise Data Dictionary, or you can see if I have time to add it to the macro for you.

Below, in the code. You can see the properties that are set for each field (Data type, name, definition, nullability, and default.)

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds four domains to aid in creating Change Tracking
'				fields.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim dict As Dictionary

	' Get the Local Data Dictionary.
	Set dict = DiagramManager.ActiveDiagram.Dictionary

	'If the Change Tracking folder does not exist, then add it.
	If (dict.DomainFolders.Item("Change Tracking") Is Nothing) Then
		dict.DomainFolders.Add("Change Tracking","Domains")
	End If

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedDTS") Is Nothing) Then
		dict.Domains.Add("InsertedDTS", "InsertedDTS")
	End If

	With dict.Domains.Item("InsertedDTS")
		.AttributeName = "InsertedDTS"
		.Datatype="DateTime"
		.DeclaredDefault = "GETDATE()"
		.Definition = "This is the date the row that was inserted."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedBy") Is Nothing) Then
		dict.Domains.Add("InsertedBy", "InsertedBy")
	End If

	With dict.Domains.Item("InsertedBy")
		.AttributeName = "InsertedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.DeclaredDefault = "SUSER_NAME()"
		.Definition = "This is the system user who created the row."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

	 ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedDTS") Is Nothing) Then
		dict.Domains.Add("ModifiedDTS", "ModifiedDTS")
	End If

	With dict.Domains.Item("ModifiedDTS")
		.AttributeName = "ModifiedDTS"
		.Datatype="DateTime"
		.Definition = "This is the date the row was modified. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

 	' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedBy") Is Nothing) Then
		dict.Domains.Add("ModifiedBy", "ModifiedBy")
	End If

	With dict.Domains.Item("ModifiedBy")
		.AttributeName = "ModifiedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.Definition = "This is the system user who modified the row. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

	Set dict = Nothing
End Sub

Tomorrow I’ll share the macro that will add these four fields to selected tables.

Previous Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

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

2 comments

  1. […] if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this […]

  2. […] attributes are based on the domains that were created by the macro in yesterday’s post found here. If you add these fields, and then decide you don’t want them, you can use my macro called Remove […]

%d bloggers like this: