Using Database Trigger to Enforce Naming Convention Standard

When setting up a SQL data warehouse that will be used by multiple developers, it is important that the naming of objects is consistent. This allows the database to be searchable and well organized so that new users can pick up on the data quickly and know how to navigate the database. To enforce a naming convention, you have a few options such as audit specifications, server events, and triggers.

I chose to use a database trigger for enforcing a naming convention because the maintenance i felt was easier. Below is the code. It checks based on the object if a prefix is there, and if not, it rolls back the transaction and alerts the user with an error:

 

CREATE TRIGGER [trg_naming_compliance]
ON DATABASE
FOR
DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS

AS
BEGIN

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA()

DECLARE @db_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(256)’)
DECLARE @evnt_typ VARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(250)’)
DECLARE @obj_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’)
DECLARE @obj_typ VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectType)[1]’, ‘varchar(250)’)
DECLARE @tsql_cmd VARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’)
DECLARE @usr_id VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)
DECLARE @obj_schema VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(256)’)

— Compliance Check

— Set your compliance prefixes here
DECLARE @TBL_PREFIX VARCHAR(5) = ‘tb_’
DECLARE @VIEW_PREFIX char(2) = ‘v_’
DECLARE @sp_prefix varchar(5) = ‘usp_’
DECLARE @FN_prefix varchar(5) = ‘ufn_’
DECLARE @COMP_VIOLATION_RULE VARCHAR(1000)

DECLARE @is_compliant BIT = 1

IF @evnt_typ IN (‘CREATE_TABLE’,’ALTER_TABLE’)
BEGIN
IF LEFT(@obj_nm,3) <> @TBL_PREFIX
SET @COMP_VIOLATION_RULE = ‘Table names must be prefixed with “tb_”.’
END
ELSE IF @evnt_typ IN (‘CREATE_VIEW’,’ALTER_VIEW’)
BEGIN
IF LEFT(@obj_nm,2) <> @VIEW_PREFIX
SET @COMP_VIOLATION_RULE = ‘View names must be prefixed with “v_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_PROCEDURE’,’ALTER_PROCEDURE’)
BEGIN
IF LEFT(@obj_nm,4) <> @sp_prefix
SET @COMP_VIOLATION_RULE = ‘Stored procedure names must be prefixed with “usp_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_FUNCTION’,’ALTER_FUNCTION’)
BEGIN
IF LEFT(@obj_nm,4) <> @FN_prefix
SET @COMP_VIOLATION_RULE = ‘Function names must be prefixed with “ufn_”.’
END
IF @COMP_VIOLATION_RULE IS NOT NULL
BEGIN
DECLARE @err_msg VARCHAR(8000) = CONCAT(‘** Compliance Violation ** : ‘,@COMP_VIOLATION_RULE,’ Please consult dev standards before resubmitting’)
SET @is_compliant = 1
RAISERROR(@err_msg,16,16)
ROLLBACK
END
END

Advertisements
Using Database Trigger to Enforce Naming Convention Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s