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

user_369 0
Viewed 74228

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.

information-schema triggers sql-server
9 Answers

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
user_133020

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')
Admin1
Related