时间戳

首页 > 解决方案 > 如何在为链接表创建的视图的 INSTEAD OF UPDATE 触发器中更新

sql - 如何在为链接表创建的视图的 INSTEAD OF UPDATE 触发器中更新

问题描述

我有表Group和Role，用外键链接。我需要通过视图更新它们，所以我必须编写 INSTEAD OF UPDATE 触发器。我目前的解决方案是错误的，我不知道如何解决它。我应该如何修复它？

表组

CREATE TABLE [Group]
(
    groupId   INT PRIMARY KEY IDENTITY,
    groupName VARCHAR(50) NOT NULL,
    picture   IMAGE       NULL,
    CONSTRAINT UniqueGroupName UNIQUE (groupName)
)

表角色

CREATE TABLE Role
(
    roleName VARCHAR(50),
    groupId  INT,
    CONSTRAINT FK_Group FOREIGN KEY (groupId) REFERENCES [Group] (groupId),
    PRIMARY KEY (roleName, groupId),
    canBan   TINYINT DEFAULT (0) /* 0 or 1 */
)

看法

CREATE VIEW GroupRoleView AS
SELECT g.groupName, r.roleName, r.canBan
FROM [Group] as g
         INNER JOIN Role AS r ON g.groupId = r.groupId
GO

当前触发器，不正确

CREATE TRIGGER UpdateGroupRoleViewTrigger
    ON GroupRoleView
    INSTEAD OF UPDATE
    AS
BEGIN
    DECLARE @tempTable TABLE
                       (
                           groupId       VARCHAR(50),
                           roleName   VARCHAR(50),
                           oldBanAbility TINYINT,
                           newBanAbility TINYINT
                       )
    INSERT INTO @tempTable (groupId, roleName, oldBanAbility, newBanAbility)
    SELECT g.groupId, deleted.roleName, deleted.canBan, inserted.canBan
    FROM deleted
             INNER JOIN inserted ON deleted.groupName = inserted.groupName
             LEFT JOIN [Group] as g on deleted.groupName = g.groupName
    SELECT * FROM @tempTable
    IF (UPDATE(roleName) OR UPDATE(groupName))
        THROW 50003, 'You cannot move role between groups or change role name. Delete existing role and create a new one instead of this.', 1
    IF (UPDATE(canBan))
        BEGIN
            --todo
            UPDATE Role
            SET canBan = (SELECT TOP 1 newBanAbility FROM @tempTable)
            WHERE groupId IN (SELECT groupId FROM @tempTable)
              AND roleName IN (SELECT roleName FROM @tempTable)
            SELECT * FROM deleted
 
        end
end
GO

标签： sqlsql-serverdatabasetsql

解决方案

从表面上看，您陷入了假设触发器表中只有一行的经典陷阱。
另请注意，触发器忽略BEGIN\END以标记触发器的结束，因此您的批处理中之后的任何代码仍然包括在内。用 结束触发器GO。
此外，如评论中所述，您没有检查值是否已更改。
在已删除/插入的查询中，您还缺少针对 roleName 的连接检查。

您的UPDATE语句也没有正确连接，在这种情况下，您根本不需要临时表。

这是正确的版本：

CREATE TRIGGER UpdateGroupRoleViewTrigger
    ON GroupRoleView
    INSTEAD OF UPDATE
    AS

    IF (NOT EXISTS (SELECT 1 FROM inserted))    -- early bail-out
        RETURN;
    SET NOCOUNT ON;    -- prevent confusion when checking rows updated in client

    IF (EXISTS (
        SELECT roleName, groupName FROM inserted
        EXCEPT
        SELECT roleName, groupName FROM deleted))
        THROW 50003, 'You cannot move role between groups or change role name. Delete existing role and create a new one instead of this.', 1;


    IF (UPDATE(canBan))
    BEGIN
        --todo
        UPDATE r
        SET canBan = i.canBan
        FROM deleted d
        -- added rolename equi-join as that appears to be correct from the schema
        INNER JOIN inserted i ON d.groupName = i.groupName AND d.roleName = i.roleName
            AND d.canBan <> i.canBan
        INNER JOIN Role r ON r.groupName = i.groupName AND r.roleName = i.roleName
    END;

GO

然而...

SQL Server 实际上支持可更新视图，因此您实际上根本不需要触发器。只要所有列都在一个表中，您就应该能够直接更新视图。

推荐阅读