What is the most portable way to check whether a trigger exists in SQL Server?
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 trINNER JOIN sys.tables t ON tr.parent_id = t.object_idWHERE t.schema_id = SCHEMA_ID('dbo') -- or whatever you need
This works on SQL Server 2000 and above
IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') = 1BEGIN ...END
Note that the naive converse doesn't work reliably:
-- This doesn't work for checking for absenseIF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') <> 1BEGIN ...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).
Assuming it is a DML trigger:
IF OBJECT_ID('your_trigger', 'TR') IS NOT NULLBEGIN PRINT 'Trigger exists'ENDELSEBEGIN 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'.