Many a time we come across a scenario where we need to execute some code based on whether a Trigger exists or not. This article explains how we can check the existence of Trigger with extensive list of examples.

To demonstrate how we can check the existence of a Trigger, let us create a sample demo database with an Inline Table Valued function by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsTriggers
GO
USE SqlHintsTriggers
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Create an After Trigger
CREATE TRIGGER AfterTriggerExample
ON Customer
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample executed!'
END
GO
--Create a Server Scoped DDL Trigger
CREATE TRIGGER DDLServerSopedTrigger
ON ALL SERVER
FOR DDL_DATABASE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLServerSopedTrigger to 
		Create , Alter or Drop database'
 ROLLBACK
END
GO

[ALSO READ] Data Manipulation Language (DML) Triggers in Sql Server

Checking the existence of a Database Scoped Triggers using sys.triggers

We can use the sys.triggers catalog view to check the existence of a Database scoped triggers. DML triggers are Database scoped triggers, where as DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with database scope like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

Example 1: Check the existence of a Database scoped Trigger using sys.triggers

We can write a query like below to check if the DML trigger AfterTriggerExample exists in the current database.

USE SqlHintsTriggers
GO
IF EXISTS (SELECT 1 FROM sys.triggers 
           WHERE Name = 'AfterTriggerExample')
BEGIN
    PRINT 'Trigger Exists'
END

RESULT:

If you want check the existence of a database scoped trigger in a database other than the contextual database then we can re-write the above query as below where sys.triggers is specified by three part name:

USE master
GO
IF EXISTS (SELECT 1 FROM SqlHintsTriggers.sys.triggers 
           WHERE Name = 'AfterTriggerExample')
BEGIN
    PRINT 'Trigger Exists'
END

RESULT:

From the above results we an see that even though the current database is MASTER database, we can check the existence of a Trigger in another database by using three part naming convention for the sys.triggers catalog view.

[ALSO READ] Data Definition Language (DDL) Triggers in Sql Server

Example 2: Try to check the existence of a Server scoped Trigger using sys.triggers

Try to execute the following query to see whether we can use the sys.triggers catalog view to check the existence of the Server scoped DDL trigger DDLServerSopedTrigger

IF EXISTS (SELECT 1 FROM sys.triggers 
           WHERE Name = 'DDLServerSopedTrigger')
	BEGIN
		PRINT 'Trigger Exists'
	END
ELSE
	BEGIN
		PRINT 'Trigger doesn''t exists'
	END

RESULT:

From the result it is clear that sys.triggers catalog view can’t find a server scoped DDL trigger even though it is present. To find server scoped DDL triggers or LOGON Triggers we can use the sys.server_triggers catalog view.

[ALSO READ] Logon Triggers in Sql Server

Example 3: Check the existence of a Server scoped Trigger using sys.server_triggers

We can write a query like below to check the existence of a Server scoped DDL trigger DDLServerSopedTrigger using the sys.server_triggers catalog view.

IF EXISTS (SELECT 1 FROM sys.server_triggers 
           WHERE Name = 'DDLServerSopedTrigger')
BEGIN
	PRINT 'Trigger Exists'
END

RESULT:

Conclusion:

From the above examples it is clear that we can use the sys.triggers catalog view to check the existence of the Database scoped triggers (i.e. DML Triggers and Database scoped DDL Triggers). Where as we need to use the sys.server_triggers catalog view to check the existene of the Server scoped triggers like Server Scoped DDL Triggers and LOGON triggers.

[ALSO READ]
How to check if a Database exists in Sql Server
How to check if a Table exists in Sql Server
How to check if Temp table exists in Sql Server
How to check if a Stored Procedure exists in Sql Server
How to check if a Function exists in Sql Server
How to check if a VIEW exists in Sql Server
How to check if a record exists in table in Sql Server