时间戳

首页 > 解决方案 > 如何使“插入”在每次插入表时返回一个值？

sql - 如何使“插入”在每次插入表时返回一个值？

问题描述

USE CIS111_BookStoreMC
GO

IF OBJECT_ID('AssetsMC_INSERT_UPDATE') IS NOT NULL
    DROP TRIGGER AssetsMC_INSERT_UPDATE
GO

CREATE TRIGGER AssetsMC_INSERT_UPDATE
    ON Assets
    AFTER INSERT,UPDATE
AS
BEGIN

    IF (((SELECT Description FROM Assets WHERE Description IN (SELECT Description FROM Inserted)) = 'Desk') AND
        ((SELECT Cost FROM Assets WHERE Cost IN (SELECT Cost FROM Inserted)) > 200.00))
 
        
    BEGIN
            RAISERROR('Desk cost should be less than $200.00', 11, 1)
            ROLLBACK TRAN   
            RETURN;     
    END
END

我确实意识到“插入”返回了多个值，因为我将多个值插入到资产表中

USE CIS111_BookStoreMC
INSERT INTO Assets
    (Description, Cost, PurchaseDate, Category, LocationID)
VALUES
    ('Laptop', 925.99, '2019-01-11', 'Computers', 1),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 2),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 1),
    ('Laptop', 925.99, '2019-01-11', 'Computers', 2),
    ('Server', 6000.00, '2019-01-11', 'Computers', 1),
    ('Server', 6000.00, '2019-01-11', 'Computers', 2),
    ('Desk', 199.99, '2019-01-16', 'Furniture', 1),
    ('Desk', 350.00, '2019-01-16', 'Furniture', 1),
    ('Desk', 199.00, '2019-01-16', 'Furniture', 2),
    ('Desk', 170.00, '2019-01-16', 'Furniture', 2),
    ('File Cabinet', 1200.00, '2019-01-16', 'Furniture', 1),
    ('File Cabinet', 1200.00, '2019-01-16', 'Furniture', 2),
    ('Water Cooler', 550.00, '2019-01-16', 'Miscellaneous', 2),
    ('Water Cooler', 560.00, '2019-01-16', 'Miscellaneous', 1)

我将如何实现此触发器以在每次插入表时运行？

标签： sqlsql-servertriggers

解决方案

您可以考虑的另一种方法是使用instead of触发器。这使您能够完全验证插入或更新，因为它根本不会发生，而是您的触发器运行并且您可以选择如何继续。

对于这种用例，我更喜欢将插入和更新操作分开，每个操作都有一个触发器。

最基本的想法是实际上只在触发器中插入或更新符合条件的行，然后对失败的行采取行动，例如引发错误 - 或者更正常地 - 只需将这些行记录到单独的表中以进行报告等。

您可能希望实现一个rules表并加入该表以确定哪些行将失败或通过。

您的触发器将如下所示

create trigger AssetsMC_INSERT
on Assets
instead of insert
as

insert into Assets (Description, Cost, PurchaseDate, Category, LocationID)
select Description, Cost, PurchaseDate, Category, LocationID
from inserted
where not exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)

if exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)
begin
    raiserror('Desk cost should be less than $200.00', 0, 1)     
end

go

create trigger AssetsMC_UNPDATE
on Assets
instead of update
as

update a set
    Description = i.Description,
    Cost = i.Cost,
    PurchaseDate = i.PurchaseDate,
    Category = i.Category,
    LocationID = i.LocationId
from inserted i join Assets a on a.Id=i.Id
where not exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)

if exists (select * from Inserted where Description = 'Desk' and Cost > 200.00)
begin
    raiserror('Desk cost should be less than $200.00', 0, 1)     
end

go

推荐阅读