SQL Server中的DDL触发器：在删除表时触发。

Question

我有一个数据库，里面有几个表：tab1、tab2、tab3。如何为仅tab2创建一个DDL触发器（而不是为tab1和tab3创建）。当调用drop tab2时，我需要更新此表中的值，但不要删除tab2。我找到了以下内容，但不理解它是如何工作的：

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
 set nocount on;
 select
  'Table dropped: ' +
  quotename(eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')) + N'.' +
  quotename(eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));
end;

谢谢！

- user3403730

---

我认为你不能使用触发器防止DROP，只能在其发生后做出反应（即仅限于AFTER触发器）。 - Joachim Isaksson

@JoachimIsaksson：我猜你得使用 DENY 来阻止删除权限，以防这种情况发生... - marc_s

@marc_s 是的，那样可以防止丢失，但也可能会阻止触发器触发更新操作。 - Joachim Isaksson

您可以使用DDL触发器防止DROP TABLE语句。正如@Sven的答案中所指出的那样，DDL触发器事件可以回滚。不需要使用DENY权限。 - Dave Mason

Answer 1

您可以使用DDL触发器来防止表的删除。例如：

CREATE TRIGGER drop_safe 
ON DATABASE 
FOR DROP_TABLE 
AS 
   PRINT 'You must disable Trigger "drop_safe" to drop table!' 
   ROLLBACK
;

如果您只想防止特定表的删除，那么您需要查看eventdata()并仅在该特殊情况下执行ROLLBACK。

- Sven

Answer 2

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
    set nocount on;

    --Get the table schema and table name from EVENTDATA()
    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    IF @Schema = 'dbo' AND @Table = 'tab2'
    BEGIN
        PRINT 'DROP TABLE Issued.';

        --Optional: error message for end user.
        RAISERROR ('[dbo].[tab2] cannot be dropped.', 16, 1);

        --Rollback transaction for the DROP TABLE statement that fired the DDL trigger
        ROLLBACK;

        --Run your update after the ROLLBACK
        BEGIN TRAN
            UPDATE dbo.tab2
            SET ... ;
        COMMIT;
    END
    ELSE
    BEGIN
        --Do nothing.  Allow table to be dropped.
        PRINT 'Table dropped: [' + @Schema + '].[' + @Table + ']';
    END
end;

- Dave Mason

Answer 3

在这篇DDL Triggers文章中，你会发现与DML触发器不同的是，没有INSTEAD OF DDL触发器，所以你无法防止删除并执行自己的命令：

DDL触发器仅在触发它们的DDL语句运行后触发。DDL触发器不能用作INSTEAD OF触发器。DDL触发器不会响应影响本地或全局临时表和存储过程的事件。

你发布的这段代码是用于记录DROP TABLE事件。 eventdata()包含有关事件的一些信息的XML：LoginName、ObjectName、TSQLCommand、PostTime等等。

- AdamL

---

实际上，您可以防止 DROP TABLE 命令。与 DDL 触发器相关联的 DDL 事件是事务操作，可以回滚。@Sven 在他的答案中展示了这一点。 - Dave Mason