主页 / dba / 问题 / 165714
Accepted
Royi Namir
Asked: 2017-03-01 04:23:17 +0800 CST

简化 SQL Server 触发器逻辑

  • 772

我有一张Images表，其中包含这些基本列 —

ImageId (int)
VotesUp (int)
VotesDown (int)

每张图片都可以获得赞成票（赞成票、反对票、撤回票）

upvotes 表有：

ImageId (int)
UserId (int)
Score (int )   [-1,0,+1]

因此，如果 -

  • 然后用户插入 +1 分数（如果不存在）我向upvotes表中添加一个新行并更新表中的值Images（以便快速获取）

  • 用户将其分数从 +1 更改为 0 然后降低votes up

  • 用户将其分数从 +1 更改为 -1，然后减少votes up和增加 votes down
  • 用户将其分数从 0 更改为 +1，然后增加votes up

  • 用户将其分数从 0 更改为 -1，然后增加votes down

  • 用户将其分数从 -1 更改为 0 然后减少votes down

  • 用户将其分数从 -1 更改为 +1，然后减少votes down 和增加votes up

这是一个非常简单的逻辑。

这是upvotes桌子上的触发器：

alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1  FROM   DELETED ) --update
BEGIN

UPDATE imgs
SET    VotesUp = CASE

                    WHEN deleted.Score = 1 AND INSERTED.score =0      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 0 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    ELSE ISNULL(VotesUp, 0)
                END  
                ,
    VotesDown = CASE 

                    WHEN deleted.Score = 0 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesDown, 0) -1
                    WHEN deleted.Score = -1 AND INSERTED.score =0      THEN ISNULL(VotesDown, 0) -1
                    ELSE ISNULL(VotesDown, 0)
                END   
FROM   Images imgs
    JOIN DELETED 
        ON  imgs.ImageId = deleted.ImageId
        JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId


END
ELSE
--insert
BEGIN
UPDATE imgs
SET    VotesUp = CASE 
                    WHEN INSERTED.Score = 1 
                    THEN ISNULL(VotesUp, 0) + 1
                    ELSE VotesUp
                END,
    VotesDown = CASE 
                    WHEN INSERTED.Score = -1 
                        THEN ISNULL(VotesDown, 0) + 1
                    ELSE VotesDown
                END
FROM   Images imgs
    JOIN INSERTED
        ON  imgs.ImageId = INSERTED.ImageId
END
END

它按预期工作。

那么问题出在哪里呢？

问题 ：

我认为这个触发器可以在没有所有这些 if's/cases 的情况下得到简化。我只是看不到简化。

是否可以简化此触发器？

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    我不使用触发器，而是使用索引视图。

    这样，数据的两个视图将由 SQL Server 自动保持同步，而无需编写任何触发代码。您也不必担心微妙的竞争条件和其他潜在的并发问题。

    索引视图

    CREATE TABLE dbo.Upvotes
(
    ImageId integer NOT NULL,
    UserId integer NOT NULL,
    Score smallint NOT NULL,

        CONSTRAINT ValidScore
        CHECK (Score IN (-1, 0, +1)),

        CONSTRAINT PK_dbo_Upvotes__ImageId_UserId
        PRIMARY KEY (ImageId, UserId)
);
GO
CREATE VIEW dbo.Images
WITH SCHEMABINDING
AS
SELECT
    U.ImageId,
    VotesUp = SUM(CASE WHEN U.Score = +1 THEN 1 ELSE 0 END),
    VotesDown = SUM(CASE WHEN U.Score = -1 THEN 1 ELSE 0 END),
    NumRows = COUNT_BIG(*)
FROM dbo.Upvotes AS U
GROUP BY 
    U.ImageId;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Images (ImageId);

    视图中的查询本质上是一个数据透视表。它不是使用PIVOT语法编写的，因为在 SQL Server 索引视图中不允许这样做。

    对于 Enterprise/Developer Edition，优化器将做出基于成本的决定是使用视图的索引，还是扩展视图并访问基表。您可以强制使用带NOEXPAND提示的视图索引。

    在其他版本中，您必须使用NOEXPAND提示来避免扩展视图。还有其他原因更喜欢NOEXPAND对索引视图使用提示。

    例子

    SELECT
    I.ImageId,
    I.VotesUp,
    I.VotesDown
FROM dbo.Images AS I WITH (NOEXPAND);

    查看索引访问

    • 7

相关问题