Каков самый переносимый способ проверить, существует ли триггер в SQL Server?

Я ищу самый переносимый метод проверки наличия триггера в MS SQL Server. Он должен работать, по крайней мере, на SQL Server 2000, 2005 и, желательно, на 2008 г.

Информация не отображается в INFORMATION_SCHEMA, но если она где-то там, я бы предпочел использовать ее оттуда.

Я знаю об этом методе:

if exists (
    select * from dbo.sysobjects 
    where name = 'MyTrigger' 
    and OBJECTPROPERTY(id, 'IsTrigger') = 1
) 
begin

end

Но я не уверен, работает ли он во всех версиях SQL Server.

Ответ 1

Это работает на SQL Server 2000 и выше

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

Заметим, что наивное обращение не работает надежно:

-- This doesn't work for checking for absense
IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') <> 1
BEGIN
    ...
END

... потому что, если объект вообще не существует, OBJECTPROPERTY возвращает NULL, а NULL (конечно) не <> 1 (или что-то еще).

В SQL Server 2005 или более поздней версии вы можете использовать COALESCE, чтобы справиться с этим, но если вам нужно поддерживать SQL Server 2000, вам нужно будет структурировать ваш оператор, чтобы иметь дело с тремя возможными возвращаемыми значениями: NULL (объект вообще не существует), 0 (он существует, но не является триггером), или 1 (это триггер).

Ответ 2

Также есть предпочтительный вид каталога "sys.triggers":

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

или вызовите хранимую процедуру sp_Helptrigger:

exec sp_helptrigger 'MyTableName'

Но кроме этого, я думаю, что об этом: -)

Марк

Обновление (для Якуба Янушкевича):

Если вам нужно включить информацию о схеме, вы также можете сделать что-то вроде этого:

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

Ответ 3

Предполагая, что это триггер DML:

IF OBJECT_ID('your_trigger', 'TR') IS NOT NULL
BEGIN
    PRINT 'Trigger exists'
END
ELSE
BEGIN
    PRINT 'Trigger does not exist'
END

Для других типов объектов (таблицы, представления, ключи, любые...), см. http://msdn.microsoft.com/en-us/library/ms190324.aspx в разделе "Тип".

Ответ 4

Протестировано и не работает на SQL Server 2000:

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

Протестировано и работает нормально на SQL Server 2000 и SQL Server 2005:

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

Ответ 5

В дополнение к отличному ответу marc_s:

если проверка существования предназначена до того, как вы отбросили или модифицировали триггер каким-либо образом, используйте прямой TSQL try/Catch bock, как самый быстрый способ.

Например:

BEGIN TRY
    DROP TRIGGER MyTableAfterUpdate;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS erno WHERE erno = 3701; -- may differ in SQL Server < 2005
END CATCH;

Сообщение об ошибке будет

Cannot drop the trigger 'MyTableAfterUpdate', because it does not exist or you do not have permission.

Затем просто проверьте, не возвратили ли результаты Executed строки или нет, что легко в прямом sql, а также программные API (С#,...).

Ответ 6

Являются ли имена триггеров вынужденными быть уникальными на SQL-сервере?

Как триггеры по определению применимы к конкретной таблице, не было бы более эффективным ограничивать поиск только рассматриваемой таблицей?

У нас есть база данных с более чем 30 тыс. таблицами, в которой есть по крайней мере один триггер и может иметь больше (плохой дизайн БД - вполне вероятно, но это имело смысл много лет назад и плохо масштабировалось)

Я использую

SELECT * FROM sys.triggers 
WHERE [parent_id] = OBJECT_ID(@tableName) 
AND [name] = @triggerName

Ответ 7

Я бы использовал этот синтаксис для проверки и удаления триггера

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCHEMA_NAME].[TRIGGER_NAME]') AND type in (N'TR'))
DROP TRIGGER [SCHEMA_NAME].[TRIGGER_NAME]

Ответ 8

Если вы пытаетесь найти серверный DDL-триггер на SQL Server 2014, вам следует попробовать sys.server_triggers.

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'your trigger name')
BEGIN
    {do whatever you want here}
END

Если я сказал что-нибудь неправильное, пожалуйста, дайте мне знать.

Изменить: Я не проверял этот dm на других версиях SQL Server.

Ответ 9

Создано Sql Server Management Studio:

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert]'))
DROP TRIGGER [dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert]
GO


CREATE TRIGGER [dbo].[RolesYAccesos2016_UsuariosCRM_trgAfterInsert] 
ON  [PortalMediadores].[dbo].[RolesYAccesos2016.UsuariosCRM]
FOR INSERT
AS  
...

Для select @@version

Microsoft SQL Server 2008 R2 (окончательная первоначальная версия) - 10.50.1797.0 (X64) 1 июня 2011 г. 15:43:18 Copyright (c) Microsoft Corporation Enterprise Edition (64-разрядная версия) в Windows NT 6.1 (сборка 7601: пакет обновления 1) (Гипервизор)