如何创建DDL触发器以阻止删除表的PK主键约束

核心实现逻辑：EVENTDATA返回的事件信息中虽然没有直接标注约束类型，但可以通过解析XML获取待删除约束的名称、所属 schema 及表名，再通过系统视图查询校验约束类型是否为主键，即可实现拦截。

完整实现代码 #

CREATE TRIGGER trg_prevent_drop_pk_constraint
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    -- 获取当前DDL操作的事件XML
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @SchemaName SYSNAME, @TableName SYSNAME;
    
    -- 读取当前操作对应的Schema和表名
    SELECT 
        @SchemaName = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
        @TableName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

    -- 临时表存储本次操作待删除的所有约束名，兼容一次删除多个约束的场景
    DECLARE @DropConstraints TABLE (ConstraintName SYSNAME);
    INSERT INTO @DropConstraints(ConstraintName)
    SELECT T.c.value('text()[1]', 'SYSNAME')
    FROM @EventData.nodes('/EVENT_INSTANCE/AlterTableActionList/Drop/Constraints/Name') T(c);

    -- 校验待删除约束中是否包含当前表的主键约束
    IF EXISTS(
        SELECT 1 
        FROM sys.key_constraints kc
        INNER JOIN @DropConstraints dc 
            ON kc.name = dc.ConstraintName
        WHERE 
            kc.schema_id = SCHEMA_ID(@SchemaName)
            AND kc.parent_object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))
            AND kc.type = 'PK' -- 系统视图中主键约束的类型标记为PK
    )
    BEGIN
        RAISERROR('不允许删除该表的主键约束', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END

可选优化 #

如果只需要保护特定表（比如示例中的a表）的主键，只需在校验条件中增加表名判断即可：

AND @TableName = 'a'

验证效果 #

1. 执行删除主键操作：

ALTER TABLE a DROP CONSTRAINT PK_x;

触发器会触发报错，操作被回滚，主键约束保留。
2. 执行删除普通非主键约束的操作不会被拦截，不影响正常业务使用。

内容的提问来源于stack exchange，提问作者George Menoutis