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

Question

I'm looking for the most portable method to check for existance 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.

So my questions are:

• Is the above the "best" way?
• Are there any alternative methods?
• What are their pros and cons?

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

Answer 2

Tested and doesn't work on SQL-2000:

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

---

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

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

Answer 3

This works on SQL2000 and above

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