尝试在插入时创建触发器时出现多部分标识符错误 [英] Multi-part identifier error when trying to create a trigger on insert

问题描述

每当将新行插入另一个表时，我都试图更新一个表.

I am trying to update a table whenever a new row is inserted into another table.

将行添加到 storeRoutes 表时，我希望此触发器更新 productList 表中的列.应当使用插入 storeRoutes 的新行中的 storeProductId 列将 isAvailable 列设置为0(否).

When a row is added to the storeRoutes table, I want this trigger to update a column in my productList table. It should set the column isAvailable to 0 (false) using the storeProductId column from the new row inserted into storeRoutes.

这是我的触发器:

CREATE TRIGGER [setIsAvailableFalse]
    ON [storeRoutes]    
    AFTER INSERT
    AS  BEGIN

    SET NOCOUNT ON;     

    UPDATE productList
    SET isAvailable = 0     
    WHERE productId = Inserted.storeProductId

END

但是我不断收到错误消息:

But I keep getting an error:

多部分标识符"Inserted.storeProductId"无法绑定.

multi-part identifier, 'Inserted.storeProductId' could not be bound.

我在做什么错了?

谢谢！

推荐答案

您需要从伪表插入的中选择:

UPDATE pl 
SET isAvailable = 0     
FROM productList pl
INNER JOIN Inserted i ON pl.productId = i.storeProductId

您还可以使用 exists :

UPDATE pl 
SET isAvailable = 0     
FROM productList pl
WHERE EXISTS (SELECT 1 FROM Inserted i WHERE  pl.productId = i.storeProductId)

这篇关于尝试在插入时创建触发器时出现多部分标识符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！