Início / dba / Perguntas / 36285
Accepted
MicBehrens
Asked: 2013-03-09 14:08:10 +0800 CST

Atualize a linha da Tabela A se a linha da Tabela B for alterada

  • 772

É possível dizer ao banco de dados para executar um pequeno script, se uma linha for alterada em uma tabela?

Cenário: O banco de dados monitora constantemente a Tabela B para ver se as linhas foram inseridas, atualizadas ou excluídas. Se uma linha for inserida, atualizada ou excluída: Atualize a linha na Tabela A com o identificador de referência.

Configurar:

Table A
------------------------
UserID   Name   SalesSum
1        Carl   5
2        Peter  0
3        Oscar  3


Table B
--------------------------
UserID   Timestamp   Sales
1        01:00:00    3
1        02:01:00    1
1        03:54:00    1
3        01:20:00    2
3        02:45:00    1

O objetivo disso é não fazer uma subconsulta toda vez que o SalesSum for necessário no aplicativo, como:

Select A.Name, SalesSum = (select sum(B.Sales) from [Table B] as B where A.UserID = B.UserID) from [Table A] as A

Mas, em vez disso, "apenas" fazendo isso de maneira fácil, agradável e limpa:

Select Name, SalesSum from [Table A]

Ou há algo que eu possa ter supervisionado ou estou errado?

EDITAR:

Muitos deles descreveram o uso de junções internas para esses subcálculos. Mas ao "tentar" isso, recebo alguns erros: primeiro, tenho que incluir cada coluna na cláusula GROUP BY, a menos que seja definida por meio de uma função agregada. Mas ao tentar fazer isso, dá erro ao ter uma coluna de tipo de dados NTEXT na cláusula, pois não pode ser comparada.

Aqui está um exemplo da consulta original:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = (select sum(C.Sales) from OrdersDetails as C),
 SalesPriceSum = (select sum(C.Sales * C.Price) from OrdersDetails as C)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Where
 ' Clauses on finding specific columns based on the Orders table and the Users table
Order By
 U.Name asc
 B.Period asc

A "nova" consulta, com outra junção interna, poderia ser algo como:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = sum(C.Sales),
 SalesPriceSum = sum(C.Sales * C.Price)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Inner join
 OrdersDetails as C on B.OrderID = C.OrderID
Where
 ' Clauses on finding specific columns in Order table
Group By
 U.UserID
 U.Name
 B.OrderID
 B.Comment     <------- This errors because of the Ntext datatype!
Order By
 U.Name asc
 B.Period asc

Conforme escrito, este é apenas um exemplo e, portanto, pode haver um grande número de colunas selecionadas - especialmente da tabela Pedidos. Tudo isso precisa estar nas cláusulas Group By. Mas isso não pode ser feito com os tipos de dados Ntext. Então o que eu faço?

sql-server-2008 sql-server-2008-r2

4 respostas

  1. Best Answer

    Eu não acho que você precise usar um gatilho para isso. Com um índice ativado B(UserID) INCLUDE(Sales)(ou se já houver um índice clusterizado liderando com UserID), essa consulta obterá o que você precisa, com bastante eficiência, sem manutenção desnecessária acontecendo o tempo todo (mesmo quando nenhuma consulta estiver sendo executada para calcular somas):

    SELECT a.UserID, a.Name, SalesSum = SUM(b.Sales)
  FROM dbo.[Table A] AS a
  INNER JOIN dbo.[Table B] AS b
  ON a.UserID = b.UserID
  GROUP BY a.UserID, a.Name;

    Olha ma, sem subconsulta!

    Isso irá gerar um plano diferente, provavelmente, mas certamente não mais caro. Não acho que você obterá tantos benefícios mantendo essa soma usando gatilhos quanto pensa. Certifique-se de testar toda a carga de trabalho, não apenas a consulta que obtém a soma, se você implementar gatilhos.

    Dito isso, aqui está a aparência de um gatilho.

    CREATE TRIGGER dbo.MaintainRedundantSums
ON dbo.[Table B]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE a 
    SET a.SalesSum = COALESCE(SUM(b.Sales), 0)
    FROM dbo.[Table A] AS a 
    LEFT OUTER JOIN dbo.[Table B] AS b
    ON a.UserID = b.UserID
    WHERE EXISTS (SELECT 1 FROM inserted WHERE UserID = a.UserID)
       OR EXISTS (SELECT 1 FROM deleted  WHERE UserID = a.UserID);
END
GO

    Por favor, teste este gatilho em um ambiente de teste; isso é bastante improvisado, em parte porque é hora do jantar e em parte porque eu realmente não quero que você use um gatilho para isso. Se você tiver um problema real de desempenho ao calcular as somas em tempo de execução, vamos falar sobre isso, em vez de otimização prematura.

    EDITAR

    Em relação à sua junção "lenta", como isso funciona em comparação?

    ;WITH d AS 
(
  SELECT OrderID, SalesAmount = SUM(Sales), SalesPriceSum = SUM(Sales*Price)
    FROM dbo.OrderDetails
    GROUP BY OrderID
)
SELECT
  u.UserID, 
  u.Name,  
  o.OrderID, 
  o.Comment,
  d.SalesAmount, 
  d.SalesPriceSum
FROM
  dbo.Users AS u
INNER JOIN 
  dbo.Orders AS o -- meaningful aliases please, not A, B, C etc.
  ON u.UserID = o.UserID
INNER JOIN
  d ON d.OrderID = o.OrderID
-- WHERE ...

-- no GROUP BY needed here
ORDER BY
  u.Name,
  o.Period;

    Você deve olhar o plano de consulta e ver se o custo está principalmente na triagem. Espero que você não tenha um índice para oferecer suporte à ordenação por u.Nameprimeiro, por exemplo. Também gostaria de verificar se você tem índices para apoiar suas WHEREcláusulas e JOINs, e certamente espero que OrderDetails.OrderIDesteja indexado adequadamente. Se você quiser ajuda para melhorar o desempenho de uma consulta (ou conjunto de consultas), poste as consultas e seus planos de execução reais (não estimados). Pular para a conclusão de que um gatilho deve ser a maneira de consertar é como comprar um carro novo quando você tem um pneu furado. Resolva o problema, não tente equipar uma solução para todo o sistema.

    • 4

  2. Normalmente, isso é tratado por gatilhos . Os gatilhos são uma ferramenta de banco de dados comum (suportada por várias plataformas RDBMS diferentes) que permitem executar ações antes ou depois das operações CRUD.

    O SQL Team tem um tutorial bastante bom sobre o uso de gatilhos.

    • 1

  3. Uma opção seria usar gatilhos de inserção/atualização/exclusão na Tabela B. Leia sobre eles aqui: http://msdn.microsoft.com/en-us/library/ms191524%28v=sql.105%29.aspx

    Gatilhos podem ser difíceis de manter, no entanto. Se você puder organizá-lo, pense em não permitir o acesso direto à Tabela B e faça com que todas as alterações na tabela ocorram por meio de procedimentos armazenados. Você pode então fazer com que o procedimento armazenado lide com as alterações que precisam ser feitas na Tabela A.

    • 0

  4. Isso não funcionaria, sem a necessidade de uma subconsulta?

    Select A.Name, sum(B.Sales)
  from [Table A] as A
  join [Table B] as b on (A.userID = B.userID)
 group by A.Name

    Usar um gatilho para manter uma coluna derivada atualizada é possível, mas oferece alguns possíveis problemas de manutenção. Por exemplo, se a tabela b for alterada, isso pode invalidar o gatilho e, assim, suas consultas estão erradas.

    • 0

relate perguntas