创建将值设置为0的INSERT触发器

Question

我有两个表:Invoice和Invoice_item，关系为1到许多。Invoice_item表有列Number_sold和Item_price, Invoice表有列Number_sold_total和Item_price_total，它们将存储来自Invoice_item表的列Number_sold和Item_price的总价值，这些列具有相同的Invoice_ID键。

CREATE TABLE [Invoice] (
[Invoice_ID] [int] NOT NULL,
[Number_sold_total] [int] NOT NULL,
[Item_price_total] [decimal] NOT NULL,
PRIMARY KEY ([Invoice_ID]));
CREATE TABLE [Invoice_item] (
[Invoice_item_ID] [int] NOT NULL,
[Invoice_ID] [int] NOT NULL,
[Number_sold] [int] NOT NULL,
[Item_price] [decimal] NOT NULL,
PRIMARY KEY ([Invoice_item_ID],[Invoice_ID],
FOREIGN KEY ([Invoice_ID]) REFERENCES [Invoice]([Invoice_ID]);

因此，如果Invoice_item中有三行具有相同的Invoice_ID，那么Invoice表中具有该Invoice_ID的行将具有Invoice_item表中相应列的SUM值。

假设我在Invoice_item表中有三行，列Item_price的值为100,200和300，并且它们的Invoice_ID = 3。Invoice中的列Item_price_total的值为600，其中Invoice_ID = 3。

——问题我的任务是在表Invoice上创建一个插入触发器，如果没有具有相应Invoice_ID的Invoice_item，则将Number_sold_total和Item_price_total的值设置为0(ZERO)。如不存在(发票)。

我正在使用SQL Server 2017.

Answer 1

理想情况下，您不会使用触发器实现此功能。

应该使用视图。如果您担心查询性能，您可以索引它，但代价是插入和删除性能。

CREATE VIEW dbo.Invoice_Totals
WITH SCHEMABINDING
AS
SELECT
i.Invoice_ID,
Number_sold = SUM(i.Number_sold),
Item_price  = SUM(i.Item_price),
ItemCount   = COUNT_BIG(*)  -- must include count for indexed view
FROM dbo.Invoice_item;

然后索引

CREAT UNIQUE CLUSTERED INDEX CX_Invoice_Totals ON Invoice_Totals
(Invoice_ID);

---

如果你真的，真的想使用触发器，你可以使用下面的

CREATE OR ALTER TRIGGER TR_Invoice_Total
ON dbo.Invoice_item
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;  -- prevent spurious resultsets
IF (NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted))
RETURN;  -- early bail-out if no rows
UPDATE i
SET Number_sold_total += totals.Number_sold_total,
Item_price_total  += totals.Item_price_total
FROM Invoice i
JOIN (
SELECT
Invoice_ID        = ISNULL(i.Invoice_ID, d.Invoice_ID),
Number_sold_total = SUM(ISNULL(i.Number_sold, 0) - ISNULL(d.Number_sold, 0)),
Item_price_total  = SUM(ISNULL(i.Item_price, 0) - ISNULL(d.Item_price, 0))
FROM inserted i
FULL JOIN deleted d ON d.Invoice_ID = i.Invoice_ID
GROUP BY
ISNULL(i.Invoice_ID, d.Invoice_ID)
) totals
ON totals.Invoice_Id = i.Invoice_ID;

,db&lt的在小提琴

触发步骤如下:

• 如果修改影响0行，则提前退出。
• 在主键上连接inserted和deleted表。这需要是一个完全连接，因为在INSERT中没有deleted，在DELETE中没有inserted行。
• 按Invoice_ID将更改行分组，取差值之和
• 连接回Invoice表
• 更新Invoice表，将总差值添加到每列。

这有效地重新创建索引视图将自动为您做的事情。

您不能只选择inserted和deleted的第一行作为变量，因为可能会有多行受到影响。你必须将它们加入并分组