WHAT IS THE BEST WAY TO CHECK WHETHER A TRIGGER EXISTS IN SQL SERVER?

John Smith

Total Post:103

Points:727
Posted by  John Smith
 3082  View(s)
Ratings:
Rate this:
I am 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 am 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?

  1. Carl Pieterson

    Post:14

    Points:98
    Re: What is the best 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 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

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!