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

Question

I'm looking for the most portable method to check for existence of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008.

The information does not appear to be in INFORMATION_SCHEMA, but if it is in there somewhere, I would prefer to use it from there.

I do know of this method:

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

end

But I'm not sure whether it works on all SQL Server versions.

marc_s · Answer 1 · 2012-01-02T08:32:03.590

65

There's also the preferred "sys.triggers" catalog view:

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

or call the sp_Helptrigger stored proc:

exec sp_helptrigger 'MyTableName'

But other than that, I guess that's about it :-)

Marc

Update (for Jakub Januszkiewicz):

If you need to include the schema information, you could 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

edited Jan 02 '12 at 08:32

answered Mar 11 '09 at 21:33

marc_s

732,580
175
1,330
1,459

`select * from sys.triggers where name = 'MyTrigger'` doesn't work for my (correctly working etc.) trigger, while wgw's `IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') = 1` does... MSSQL 2008 R2. – Jakub Januszkiewicz Dec 02 '11 at 12:39
@JakubJanuszkiewicz: are you in the right DB when you run this?? `sys.triggers` always shows the trigger in your current DB - it doesn't show all triggers from all databases... – marc_s Dec 02 '11 at 15:44
@marc-s: I was in the correct DB. I have found the problem - the `name` column in `sys.triggers` is just a name (without schema name), while `OBJECT_ID('...')` expects a schema-qualified name (at least if the schema if a non-default one, if I understand it right). So after I copied my working `OBJECT_ID('MySchema.MyTrigger')` to `select * from sys.triggers`, it didn't work. Filtering by just 'MyTrigger' works fine. – Jakub Januszkiewicz Jan 02 '12 at 06:53
3

@marc-s: By the way, this also means that if you have more than one trigger with the same name in different schemas of a DB, `select * from sys.triggers` will give you a false positive. Something along the lines of `select * from sys.objects where type = 'TR' and schema_id = (select schema_id from sys.schemas where name = 'YourSchema') and name = 'YourTrigger'` will get the correct trigger. – Jakub Januszkiewicz Jan 02 '12 at 06:54
2

This will not work in SQL Server 2000, as required in the question, as the sys... catalog views were introduced in SQL Server 2005. – Simon Elms Apr 26 '13 at 11:24
Also, `sys.tables` can be replaced with `sys.views` when for example a `instead of insert` trigger is created for view. – gotqn Jan 02 '14 at 09:19

score 34 · Accepted Answer · edited Sep 05 '14 at 08:45

This works on SQL Server 2000 and above

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

Note that the naive converse doesn't work reliably:

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

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

On SQL Server 2005 or later, you could use COALESCE to deal with that, but if you need to support SQL Server 2000, you'll have to structure your statement to deal with the three possible return values: NULL (the object doesn't exist at all), 0 (it exists but is not a trigger), or 1 (it's a trigger).

[Important Caveat](https://msdn.microsoft.com/en-us/library/ms190328(v=sql.100).aspx): "*Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID.*" The workaround in these cases is to use the `sys.triggers` view as @marc_s describes in his answer. — mwolfe02, Jul 01 '15 at 20:43

score 9 · Answer 3 · edited Oct 30 '12 at 15:06

9

Assuming it 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 under 'type'.

edited Oct 30 '12 at 15:06

beruic

5,517
3
35
59

answered Nov 12 '11 at 23:27

BodzioM

91
1
1

1

This checks any object type, not only triggers. – Jakub Januszkiewicz Jan 02 '12 at 06:56
it prints 'Trigger exists', works in sql server 2012 – Shaiju T Mar 08 '15 at 12:44

score 2 · Answer 4 · answered Dec 08 '13 at 12:29

In addition to the excellent answer by marc_s:

if the existence check is intended prior to dropping or modifying the trigger in some way, use a direct TSQL try/Catch bock, as the fastest means.

For instance:

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 simply check if the Executed Result returned rows or not, which is easy in direct sql as well as the programmatic APIs (C#,...).

score 2 · Answer 5 · answered Jul 16 '14 at 17:31

If you're trying to find a server scoped 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 told tou anything incorrect, please let me know.

Edit: I didn't check for this dm on another versions of SQL Server.

score 2 · Answer 6 · edited Sep 12 '13 at 15:44

2

Tested and doesn't work on SQL Server 2000:

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

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

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

edited Sep 12 '13 at 15:44

marc_s

732,580
175
1,330
1,459

answered Apr 09 '09 at 09:03

1

FWIW, your `select * from dbo.sysobjects where name = 'MyTrigger' and OBJECTPROPERTY(id, 'IsTrigger')` doesn't work for my correctly-functioning `after insert, delete` trigger on SQL-2000. The row is there in `sysobjects`, but `OBJECTPROPERTY(id, 'IsTrigger')` on its ID (as part of the above or separately just using its raw ID) gives `0`. Checking for `xtype = 'TR'` or `type = 'TR'` works. – T.J. Crowder Apr 24 '10 at 16:56

score 1 · Answer 7 · answered Feb 04 '13 at 10:58

Are trigger names forced to be unique in SQL server?

As triggers are by definition applied to a specific table would it not be more efficient to restrict the search to only the table in question?

We have a database with over 30k tables in it all of which have at least one trigger and may have more (bad DB design - quite probably, but it made sense years ago and didn't scale well)

I use

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

score 1 · Answer 8 · answered Jan 15 '14 at 08:09

1

I would use this syntax to check and drop 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]

answered Jan 15 '14 at 08:09

Arvind Sedha

101
1
3

score 0 · Answer 9 · answered Feb 24 '16 at 11:20

Generated 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) Jun 1 2011 15:43:18 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

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

9 Answers9

Linked