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.
Best Answer
There's also the preferred "sys.triggers" catalog view:
or call the sp_Helptrigger stored proc:
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: