SQL 删除触发器（如果存在）并创建
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31052934/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
drop trigger if exists and create
提问by user3399326
I would like to check if the trigger exists on [tbl] and create another one. I tried it this way but didn't work. What am I doing wrong?
我想检查 [tbl] 上是否存在触发器并创建另一个触发器。我以这种方式尝试过，但没有奏效。我究竟做错了什么？
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = '[dbo].[trg]' AND [type] = 'TR')
DROP TRIGGER [dbo].[trg] ON [dbo].[tbl]
GO
CREATE TRIGGER [dbo].[trg] ON [dbo].[tbl]
AFTER DELETE
AS
BEGIN
//
END
GO
回答by Solomon Rutzky
The [name]field in sys.objectswill contain only the actual name (i.e. trg), notincluding the schema (i.e. dboin this case) or any text qualifiers (i.e. [and ]in this case).
在[name]现场sys.objects将只包含实际名称（即trg），不包括架构（即dbo在这种情况下）或者任何文本识别符（即[和]在这种情况下）。
AND, you don't specify the table name for DROP TRIGGERsince the trigger is an object by itself (unlike indexes). So you need to remove the ONclause (which is only used with DDL and Logon triggers).
并且，您没有指定表名，DROP TRIGGER因为触发器本身就是一个对象（与索引不同）。所以你需要删除ON子句（它只用于 DDL 和登录触发器）。
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'trg' AND [type] = 'TR')
BEGIN
DROP TRIGGER [dbo].[trg];
END;
Please note that you should prefix the object name string literal with an Nsince the [name]field is a sysnamedatatype which equates to NVARCHAR(128).
请注意，您应该使用 an 作为对象名称字符串文字的前缀，N因为该[name]字段是一种sysname等同于的数据类型NVARCHAR(128)。
If you did want to incorporate the schema name, you could use the OBJECT_ID()function which does allow for schema names and text qualifiers (you will then need to match against object_idinstead of name):
如果您确实想合并架构名称，则可以使用OBJECT_ID()允许架构名称和文本限定符的函数（然后您需要匹配object_id而不是匹配name）：
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[trg]')
AND [type] = 'TR')
BEGIN
DROP TRIGGER [dbo].[trg];
END;
And to simplify, since the object name needs to be unique within the schema, you really only need to test for its existence. If for some reason a different object type exists with that name, the DROP TRIGGERwill fail since that other object is, well, not a trigger ;-). Hence, I use the following:
为简化起见，由于对象名称在模式中必须是唯一的，因此您实际上只需要测试它是否存在。如果由于某种原因存在具有该名称的不同对象类型，则DROP TRIGGER该对象将失败，因为该其他对象不是触发器;-)。因此，我使用以下内容：
IF (OBJECT_ID(N'[dbo].[trg]') IS NOT NULL)
BEGIN
DROP TRIGGER [dbo].[trg];
END;
回答by Neshta
If you use SQL Server 2016, you can use a shorter variant.
如果您使用 SQL Server 2016，则可以使用较短的变体。
DROP TRIGGER IF EXISTS [dbo].[trg]
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql
