SA0065B : Check trigger names used in CREATE TRIGGER statements for following specified naming convention

Introduction

Ensure consistent trigger naming convention to avoid confusion and maintenance challenges.

Description

Triggers are special types of stored procedures that automatically execute when certain events occur in the database. In SQL Server, using consistent and meaningful naming conventions for these triggers is crucial for database readability, maintenance, and debugging.

For example:

-- Poorly named trigger
CREATE TRIGGER trg1 ON TableName AFTER INSERT AS BEGIN
    -- Trigger logic
END;

In the example above, the trigger name trg1 lacks context about its purpose or the events it handles. This can make it difficult for developers and DBAs to understand the role of the trigger quickly.

Lack of clarity: Vague or non-descriptive names can lead to confusion about what the trigger does or when it fires.
Increased maintenance: Poor naming conventions make it harder to manage and debug triggers, especially in large databases with many triggers.

How to fix

Rename triggers using clear and meaningful naming conventions to improve readability, maintenance, and debugging in SQL Server.

Follow these steps to address the issue:

1.Identify the trigger that uses a vague or non-descriptive name from your database. You can do this by querying the sys.triggers system view to find trigger names. For example:

2.Determine a naming convention that reflects the trigger’s purpose and the event that activates it. A possible format could be tr_ , followed by the table name, and then the event, like tr_TableName_AFTER_INSERT .

3.Rename the trigger using the sp_rename system stored procedure. For example, you can rename trg1 to a more descriptive name:

For example:

-- Rename the trigger to a more descriptive name
EXEC sp_rename
    @objname = N'TableName.trg1',
    @newname = 'tr_TableName_AFTER_INSERT';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
DmlTriggerNamePattern	Table triggers name pattern.	trig_{trigger_events}{table_name}
DmlTriggerSchemaQualifiedNamePattern	DmlTrigger schema qualified name pattern.	-
DdlTriggerNamePattern	Database and server triggers name pattern.	regexp:{triggerevents}[A-Z]A-Za-z1-9]+
InsertEventName	Event name which to be used in the {trigger_events} placeholder for the specific event type.	i
UpdateEventName	Event name which to be used in the {trigger_events} placeholder for the specific event type.	u
DeleteEventName	Event name which to be used in the {trigger_events} placeholder for the specific event type.	d
TriggerEventsListSeparator	Separator for the event in the {trigger_events} placeholder.
DdlTriggerEventNames	Specifies how the trigger event types are set in the {trigger_events} placeholder.	Use first letter of each word (lowercase)

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK

Analysis Results

	Message	Line	Column
1	SA0065B : The trigger [safety] does not match the naming convention. The expected key name is [ds[A-Z][A-Za-z1-9_]+].	1	15

Analysis Rules