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.

Answer 1

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 

Answer 2

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).