EX0007 : List all DML and DDL triggers in current database

Introduction

The rule provides an overview of all triggers in the current database. Having a clear list of triggers enables better control, planning, and optimization of database functionality.

Description

Triggers in SQL Server can automatically execute a batch of SQL statements in response to certain events on a particular table or view, like inserts, updates, or deletes. If not properly managed, these triggers can lead to performance issues and make debugging more difficult.

Example of a table with a trigger that may cause issues:

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    DECLARE @empCount INT
    SELECT @empCount = COUNT(*) FROM Employees
    PRINT 'Total Employees: ' + CONVERT(VARCHAR, @empCount)
END;

In this example, the trigger counts the rows in the Employees table after every insert, which could lead to significant performance degradation if the table is large or inserts occur frequently.

Triggers can create hidden dependencies, making it hard to predict the database behavior.
Excessive use of triggers may result in performance bottlenecks, especially for transactions involving large tables.

How to fix

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
IncludeEvents	Speecify whether to include trigger events in the rule message.	yes

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

Not configured.

Additional Information

There is no additional info for this rule.

Analysis Rules