多部分标识符无法绑定

Question

在我的代码下面，我得到一个错误:

多部分标识符"i.Inventoryid"无法绑定。

CREATE TRIGGER trg_UpdateOrderMultiple
ON tbl_InventoryPolicy
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @OldOrderMultiple INT
    DECLARE @NewOrderMultiple INT
    DECLARE @Timestamp DATETIME
    SELECT @OldOrderMultiple = d.ordermultiple
    FROM deleted d
    INNER JOIN inserted i ON i.Inventoryid = d.Inventoryid
    SELECT @NewOrderMultiple = i.ordermultiple
    FROM inserted i
    SET @Timestamp = GETDATE()
IF NOT EXISTS (
SELECT 1
FROM tbl_InventoryPolicyChanges
WHERE Inventoryid = i.Inventoryid
AND OldOrderMultiple = @OldOrderMultiple
AND NewOrderMultiple = @NewOrderMultiple
)
BEGIN
INSERT INTO tbl_InventoryPolicyChanges 
(InventoryID, OldOrderMultiple, NewOrderMultiple, Timestamp)
SELECT i.InventoryID, @OldOrderMultiple, 
@NewOrderMultiple, @Timestamp
FROM inserted i
END
END

我想避免在tbl_InventoryPolicyChanges中插入重复的条目。

Answer 1

您正在犯许多人在触发器时犯的典型错误，那就是突然从常规的基于SQL集的操作转变为某种过程逻辑。不要这样做，继续像处理其他查询一样使用基于集合的操作。我认为下面的代码将您现有的逻辑转换为基于集合的逻辑，但您需要自己验证它。

1. 在需要时使用与Inserted和Deleted伪表的连接

2. 添加where子句来过滤不需要的更改

INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
FROM Inserted i
INNER JOIN Deleted d on d.InventoryId = i.InventoryId
WHERE NOT EXISTS (
SELECT 1
FROM tbl_InventoryPolicyChanges
WHERE Inventoryid = i.Inventoryid
AND NewOrderMultiple = i.ordermultiple
AND OldOrderMultiple = d.ordermultiple
);

注意:您的触发器也是一个插入触发器，但您的逻辑不处理插入，因为您假设Deleted将在其中有行。如果它还应该处理插入，则需要修改逻辑以处理Deleted伪表中的任何行。

我认为下面也处理INSERT的情况，注意LEFT JOIN和null比较:

INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
FROM Inserted i
LEFT JOIN Deleted d on d.InventoryId = i.InventoryId
WHERE NOT EXISTS (
SELECT 1
FROM tbl_InventoryPolicyChanges c
WHERE c.Inventoryid = i.Inventoryid
AND c.NewOrderMultiple = i.ordermultiple
AND (c.OldOrderMultiple = d.ordermultiple OR (d.ordermultiple IS NULL AND c.OldOrderMultiple IS NULL))
);