如何使用临时表设置触发器-解网

问：

我正在尝试为数据库设置触发器（我正在使用 Microsoft SQL Server）。

我有 2 张桌子

create table atbv_Sales_Products 
(
     ProductID integer, 
     TotalQuantity integer
);

insert into atbv_Sales_Products values (1, 1);
insert into atbv_Sales_Products values (2, 2);
insert into atbv_Sales_Products values (3, 20);
insert into atbv_Sales_Products values (4, 10);
insert into atbv_Sales_Products values (5, 20);
insert into atbv_Sales_Products values (6, 10);
insert into atbv_Sales_Products values (7, 5);
insert into atbv_Sales_Products values (8, 50);
insert into atbv_Sales_Products values (9, 1);

create table atbv_Sales_OrdersLines 
(
     OrderID integer, 
     ProductID integer, 
     Amount integer
);

insert into atbv_Sales_OrdersLines values (6, 4, 1);
insert into atbv_Sales_OrdersLines values (6, 6, 1);
insert into atbv_Sales_OrdersLines values (6, 1, 1);
insert into atbv_Sales_OrdersLines values (47, 4, 1);
insert into atbv_Sales_OrdersLines values (6, 9, 1);
insert into atbv_Sales_OrdersLines values (5, 7, 1);
insert into atbv_Sales_OrdersLines values (6, 2, 2);

还有一个插入表（它实际上是自动生成的，但为了清楚起见，让我们把它放在这里

create table Inserted 
(
     OrderID integer, 
     ProductID integer, 
     Amount integer
);

insert into Inserted values (48, 4, 9);
insert into Inserted values (48, 1, 10);
insert into Inserted values (48, 8, 100);
insert into Inserted values (48, 2, 1);

为了便于理解，以下是这些表格的图形外观：

产品表

Products table

OrdersLines 表

OrdersLines table

插入表格

Insert table

现在，触发器应检查之前插入的 Amount 值 + Amount 值是否超过 TotalQuantity（这是一个静态值。或者换句话说，当新订单进来时，它不会改变），如果是这样，则回滚更改

为了过滤，我使用了这部分代码

IF EXISTS (select p.ProductID 
           from atbv_Sales_Products p 
           join Inserted i
           on p.ProductID = i.ProductID
           join atbv_Sales_OrdersLines ol
           on p.ProductID = i.ProductID
           group by i.ProductID, i.Amount, p.TotalQuantity
           having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity)

然后我一直在尝试使用代码的以下代码部分来回滚更改并发出错误警报

BEGIN
    DECLARE @ProductID NVARCHAR(60)
            SET @ProductID = (SELECT p.ProductID 
                                    FROM atbv_Sales_Products p
                                    JOIN inserted i 
                                    ON i.ProductID = p.ProductID)
            RAISERROR ('----There is not enough items number (%s) left----', 18, 1, @ProductName) ROLLBACK TRANSACTION 
            RETURN
END

如果只有插入一行，这很好用，但如果有多行，如当前示例中，我不知道该怎么办。我想我已经在某处读到我可以创建一个临时表，但同样，不知道如何将代码的第一部分过滤掉的那些值插入其中，然后使用这些值显示在错误消息中。

sql-server t-sql

declare @BadProducts varchar(max)

select @BadProducts = 
    stuff((select ',' + p.ProductId
           from atbv_Sales_Products p 
           join Inserted i
           on p.ProductID = i.ProductID
           join atbv_Sales_OrdersLines ol
           on p.ProductID = i.ProductID
           group by i.ProductID, i.Amount, p.TotalQuantity
           having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity
           for xml path('')), 1, 1, '')

if @BadProducts is not null
begin
    raiserror('These products are bad: %s', 16, 1, @BadProducts)
    return
end

另一种方法是将行插入到临时表或表变量中（如您建议的那样），然后对临时表进行存在性检查或检查，如果匹配，则以与第一个示例大致相同的方式序列化产品 ID（除了将整个大子查询替换为临时表）。像这样的东西：@@rowcount

declare @badProductsTable table
(
    ProductId int
)

insert into @badProductsTable (ProductId)
select p.ProductId
from atbv_Sales_Products p 
join Inserted i
on p.ProductID = i.ProductID
join atbv_Sales_OrdersLines ol
on p.ProductID = i.ProductID
group by i.ProductID, i.Amount, p.TotalQuantity
having (SUM(ol.Amount) + i.Amount) > p.TotalQuantity

if @@rowcount > 0
    -- or you could do
    -- if exists (select 1 from @badProductsTable)
begin
    select @BadProducts = 
        stuff((select ',' + ProductId
               from @badProductsTable
               for xml path('')), 1, 1, '')

    raiserror('These products are bad: %s', 16, 1, @BadProducts)
    return

end

右。因此，当您使用时，它会连接每个 .麻烦的是，如果你不输入分隔符，你最终会让产品 ID 1、2 和 3 成为字符串，而你真正想要的是 .因此，您可以在每个产品 ID 前面加上逗号。现在，其他编程语言已经足够好了，可以知道你不需要前导逗号，但SQL不需要。因此，您可以使用该函数将第一个字符（即第一个逗号）替换为空字符串。FOR XMLProductId'123''1,2,3'STUFF()

0赞 DeadZone 8/4/2017 #2

您的 If Exists 语句确实是您所需要的。这个脚本对我有用......

-- DROP table atbv_Sales_Products 
create table atbv_Sales_Products 
(
     ProductID integer, 
     TotalQuantity integer
);

insert into atbv_Sales_Products values (1, 10);

-- DROP table atbv_Sales_OrdersLines 
create table atbv_Sales_OrdersLines 
(
     OrderID integer, 
     ProductID integer, 
     Amount integer
);

insert into atbv_Sales_OrdersLines values (100, 1, 4);


CREATE TRIGGER MyTrigger ON atbv_Sales_OrdersLines AFTER INSERT, UPDATE AS
    IF EXISTS (
        SELECT      *
        FROM        (SELECT ProductID, SUM(Amount) as [Amount] FROM Inserted GROUP BY ProductID) I
        LEFT JOIN   (SELECT ProductID, SUM(Amount) as [Amount] FROM atbv_Sales_OrdersLines X WHERE NOT EXISTS (SELECT * FROM Inserted Y WHERE Y.OrderID = X.OrderID) GROUP BY ProductID) OL ON OL.ProductID = I.ProductID
        JOIN        atbv_Sales_Products P ON P.ProductID = I.ProductID
        WHERE       I.Amount + ISNULL(OL.Amount, 0) > P.TotalQuantity
    )
        THROW 51000, 'My Error Message.', 1;
GO

-------------

SELECT * FROM atbv_Sales_Products
SELECT * FROM atbv_Sales_OrdersLines

insert into atbv_Sales_OrdersLines values (101, 1, 4);
insert into atbv_Sales_OrdersLines values (102, 1, 2);
insert into atbv_Sales_OrdersLines values (103, 1, 1);

此外，请考虑找到一种使用 CHECK 约束而不是触发器的方法。触发器通常会导致维护问题和性能问题。

最后，这个例子只是学术性的。由于订单在多天内收到并且可用数量波动，因此此解决方案将无法很好地工作。它有助于说明一个观点，但我希望这只是出于学术目的。

如何使用临时表设置触发器

How to setup trigger using temporary table

评论

评论

评论