What is the most portable way to check if a trigger exists in SQL Server?

Question

I am looking for the most portable trigger checking method in MS SQL Server. It should work, at least on SQL Server 2000, 2005, and preferably 2008.

Information is not displayed in INFORMATION_SCHEMA, but if it is somewhere out there, I would prefer to use it from there.

I know about this method:

if exists ( select * from dbo.sysobjects where name = 'MyTrigger' and OBJECTPROPERTY(id, 'IsTrigger') = 1 ) begin end 

But I'm not sure if it works in all versions of SQL Server.

Answer 1

This works on SQL Server 2000 and later.

 IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') = 1 BEGIN ... END 

Note that naive handling does not work reliably:

 -- This doesn't work for checking for absense IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') <> 1 BEGIN ... END 

... because if the object does not exist at all, OBJECTPROPERTY returns NULL , and NULL (of course) is not <> 1 (or something else).

In SQL Server 2005 or later, you can use COALESCE to handle this, but if you need to support SQL Server 2000, you will need to structure your statement to deal with three possible return values: NULL (the object does not exist at all) , 0 (it exists, but is not a trigger), or 1 (it is a trigger).

Answer 2

There is also a preferred view of the sys.triggers directory:

 select * from sys.triggers where name = 'MyTrigger' 

or call the sp_Helptrigger stored procedure:

 exec sp_helptrigger 'MyTableName' 

But besides this, I think about it :-)

Mark

Update (for Yakub Yanushkevich):

If you need to include schema information, you can also do something like this:

 SELECT (list of columns) FROM sys.triggers tr INNER JOIN sys.tables t ON tr.parent_id = t.object_id WHERE t.schema_id = SCHEMA_ID('dbo') -- or whatever you need 

Answer 3

Assuming this is a DML trigger:

 IF OBJECT_ID('your_trigger', 'TR') IS NOT NULL BEGIN PRINT 'Trigger exists' END ELSE BEGIN PRINT 'Trigger does not exist' END 

For other types of objects (tables, views, keys, whatever ...), see http://msdn.microsoft.com/en-us/library/ms190324.aspx in the "Type" section.

Answer 4

Tested and not working on SQL Server 2000:

 select * from sys.triggers where name = 'MyTrigger' 

---

Tested and works fine on SQL Server 2000 and SQL Server 2005:

 select * from dbo.sysobjects where name = 'MyTrigger' and OBJECTPROPERTY(id, 'IsTrigger') 

Answer 5

In addition to marc_s excellent answer:

if the existence check is intended before you drop or modify the trigger in any way, use the direct TSQL try/Catch bock as the fastest way.

For example:

 BEGIN TRY DROP TRIGGER MyTableAfterUpdate; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS erno WHERE erno = 3701; -- may differ in SQL Server < 2005 END CATCH; 

The error message will be

 Cannot drop the trigger 'MyTableAfterUpdate', because it does not exist or you do not have permission. 

Then just check if the results of the Executed string returned or not, which is easy in direct sql, as well as the programming APIs (C #, ...).

Answer 6

Are trigger names forced to be unique on the SQL server?

As triggers are by definition applicable to a particular table, would it not be more efficient to limit the search to only the table in question?

We have a database with more than 30 thousand tables, in which there is at least one trigger and may have more (a poor database design is likely, but it made sense many years ago and did not scale well)

I use

 SELECT * FROM sys.triggers WHERE [parent_id] = OBJECT_ID(@tableName) AND [name] = @triggerName 

Answer 7

I would use this syntax to check and remove a trigger

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCHEMA_NAME].[TRIGGER_NAME]') AND type in (N'TR')) DROP TRIGGER [SCHEMA_NAME].[TRIGGER_NAME] 

Answer 8

If you are trying to find a server DDL trigger on SQL Server 2014, you should try sys.server_triggers.

 IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'your trigger name') BEGIN {do whatever you want here} END 

If I said anything wrong, please let me know.

Edit: I have not tested this dm on other versions of SQL Server.

Answer 9

Created by Sql Server Management Studio :

 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert]')) DROP TRIGGER [dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert] GO CREATE TRIGGER [dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert] ON [PortalMediadores].[dbo].[RolesYAccesos2016.UsuariosCRM] FOR INSERT AS ... 

For select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (X64) June 1, 2011 15:43:18 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (build 7601: package Updates 1) (Hypervisor)