什么是在SQL Server中检查触发器是否存在的最便携方式？

Question

我正在寻找在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版本。

- Blorgbeard

Answer 1

还有一个首选的“sys.triggers”目录视图：

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

或者调用sp_Helptrigger存储过程：

exec sp_helptrigger 'MyTableName'

除此之外，我想这就是全部了 :-)

Marc

更新（给Jakub Januszkiewicz）：

如果您需要包含模式信息，也可以这样做：

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

- marc_s

---

我的触发器（已经正常工作）使用 select * from sys.triggers where name = 'MyTrigger' 无法正常工作，但是 wgw 的 IF OBJECTPROPERTY(OBJECT_ID('{your_trigger}'), 'IsTrigger') = 1 可以正常工作。我使用的是 MSSQL 2008 R2。 - Jakub Januszkiewicz

@JakubJanuszkiewicz：你在运行时使用正确的数据库了吗？ sys.triggers 总是显示当前数据库中的触发器 - 它不会显示来自所有数据库的所有触发器... - marc_s

@marc-s：我在正确的数据库中。我已经找到了问题——sys.triggers中的name列只是一个名称（没有模式名称），而OBJECT_ID('...')期望一个带有模式限定符的名称（至少如果模式是非默认的，如果我理解得正确的话）。所以当我将我的工作OBJECT_ID('MySchema.MyTrigger')复制到select * from sys.triggers时，它不起作用。仅按“ MyTrigger”过滤可以正常工作。 - Jakub Januszkiewicz

顺便说一下，这也意味着，如果在数据库的不同模式中有多个具有相同名称的触发器，则“select * from sys.triggers”将会给你一个误报。类似于“select * from sys.objects where type = 'TR' and schema_id = (select schema_id from sys.schemas where name = 'YourSchema') and name = 'YourTrigger'”将会得到正确的触发器。 - Jakub Januszkiewicz

这在 SQL Server 2000 中不起作用，因为问题所需的 sys... 目录视图是在 SQL Server 2005 中引入的。 - Simon Elms

此外，当为视图创建“instead of insert”触发器时，可以将sys.tables替换为sys.views。 - gotqn

Answer 2

此方法适用于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（它是一个触发器）。

- wqw

---

重要提示：未经模式范围限定的对象（例如DDL触发器）无法使用OBJECT_ID进行查询。在这些情况下的解决方法是使用sys.triggers视图，正如@marc_s在他的答案中所描述的那样。 - mwolfe02

Answer 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 中的“类型”部分。

- BodzioM

---

这个检查适用于任何对象类型，不仅仅是触发器。 - Jakub Januszkiewicz

它会打印“触发器已存在”，适用于SQL Server 2012。 - Shaiju T

Answer 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')

- EJM

---

就我所知，你的“select * from dbo.sysobjects where name = 'MyTrigger' and OBJECTPROPERTY(id, 'IsTrigger')”在我的SQL-2000上无法正确运行，而我的“after insert, delete”触发器却可以正常工作。该行在“sysobjects”中存在，但是对其ID使用“OBJECTPROPERTY（id，'IsTrigger'）”（作为上述内容的一部分或单独使用其原始ID）会返回“0”。检查“xtype ='TR'”或“type ='TR'”可行。 - T.J. Crowder

Answer 5

如果您正在尝试在SQL Server 2014上查找服务器范围的DDL触发器，则应该尝试使用sys.server_triggers。

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

如果我说错了什么，请告诉我。

编辑： 我没有在其他版本的SQL Server上检查这个dm。

- Diego Grigol

Answer 6

除了marc_s提供的出色答案外：

如果在删除或修改触发器之前需要进行"存在性检查"，则使用直接的TSQL try/Catch块是最快的方法。

例如：

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.

然后只需检查执行结果是否返回了行，这在直接 SQL 和编程 API（如 C# 等）中都很容易。

- Lorenz Lo Sauer

Answer 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]

- Arvind Sedha

Answer 8

在SQL Server中，触发器名称是否强制唯一？

由于触发器按定义应用于特定表格，将搜索限制在仅涉及的表格内是否更有效率？

我们的数据库中有超过30k个表格，其中每个表格至少有一个触发器，并且可能还有更多（可能是错误的数据库设计，但在很多年前是有意义的，而且没有良好的扩展性）

我使用

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

- Morvael

Answer 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（RTM）-10.50.1797.0（X64）Jun 1 2011 15:43:18版权所有（c）Microsoft Corporation企业版 （64位）运行在Windows NT 6.1（Build 7601：Service Pack 1） （Hypervisor）上

- Kiquenet