Início / dba / Perguntas / 165714
Accepted
Royi Namir
Asked: 2017-03-01 04:23:17 +0800 CST

Simplifique a lógica de gatilho do SQL Server

  • 772

Eu tenho uma Imagestabela onde essa tabela tem essas colunas básicas -

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

Cada imagem pode receber votos positivos (up, down, retrair vote)

A tabela de upvotes tem:

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

Então se -

  • Um usuário insere uma pontuação de +1 então (se não existir) eu adiciono uma nova linha à upvotestabela e atualizo o valor na Imagestabela (para busca rápida)

  • Um usuário altera sua pontuação de +1 para 0 e depois diminuivotes up

  • Um usuário altera sua pontuação de +1 para -1 e depois diminui votes upe aumenta votes down
  • Um usuário altera sua pontuação de 0 para +1 e depois aumentavotes up

  • Um usuário altera sua pontuação de 0 para -1 e depois aumentavotes down

  • Um usuário altera sua pontuação de -1 para 0 e depois diminuivotes down

  • Um usuário altera sua pontuação de -1 para +1 e depois diminui votes down e aumentavotes up

É uma lógica bem simples.

Aqui está o gatilho na upvotestabela:

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

Está funcionando como esperado.

Então onde está o problema ?

Pergunta :

Acho que esse gatilho pode ser simplificado sem todos esses if's/cases . Não consigo ver a simplificação.

É possível simplificar este gatilho?

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Em vez de usar um gatilho, eu usaria uma exibição indexada.

    Dessa forma, as duas visualizações dos dados são mantidas automaticamente sincronizadas pelo SQL Server, sem escrever nenhum código de gatilho. Você também não precisa se preocupar com condições sutis de corrida e outros possíveis problemas de simultaneidade.

    Visualização indexada

    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);

    A consulta na exibição é essencialmente um pivô. Ele não é escrito usando PIVOTsintaxe, pois isso não é permitido em uma exibição indexada do SQL Server.

    Para Enterprise/Developer Edition, o otimizador tomará uma decisão baseada em custo para usar o(s) índice(s) da exibição ou expandir a exibição e acessar as tabelas base. Você pode forçar o uso dos índices de exibição com uma NOEXPANDdica.

    Em outras edições você deve usar a NOEXPANDdica para evitar expandir a visualização. Há outros motivos para preferir usar NOEXPANDdicas com exibições indexadas.

    Exemplo

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

    Visualizar acesso ao índice

    • 7

relate perguntas