“The multi-part identifier could not be bound error for duplicates.”

huangapple
go评论89阅读模式
英文:

The multi-part identifier could not be bound error for duplicates

问题

在我的代码中，我遇到了一个错误：

> 多部分标识符“i.Inventoryid”无法绑定。

  1. CREATE TRIGGER trg_UpdateOrderMultiple
  2. ON tbl_InventoryPolicy
  3. AFTER INSERT, UPDATE
  4. AS
  5. BEGIN
  6.     DECLARE @OldOrderMultiple INT
  7.     DECLARE @NewOrderMultiple INT
  8.     DECLARE @Timestamp DATETIME
  10.     SELECT @OldOrderMultiple = d.ordermultiple
  11.     FROM deleted d
  12.     INNER JOIN inserted i ON i.Inventoryid = d.Inventoryid
  14.     SELECT @NewOrderMultiple = i.ordermultiple
  15.     FROM inserted i
  17.     SET @Timestamp = GETDATE()
  18.     IF NOT EXISTS (
  19.         SELECT 1
  20.         FROM tbl_InventoryPolicyChanges
  21.         WHERE Inventoryid = i.Inventoryid
  22.         AND OldOrderMultiple = @OldOrderMultiple
  23.         AND NewOrderMultiple = @NewOrderMultiple
  24.     )
  25.     BEGIN
  26.         INSERT INTO tbl_InventoryPolicyChanges 
  27.                     (InventoryID, OldOrderMultiple, NewOrderMultiple, Timestamp)
  28.         SELECT i.InventoryID, @OldOrderMultiple, 
  29.                 @NewOrderMultiple, @Timestamp
  30.         FROM inserted i
  31.     END
  32. END

我希望避免将重复的条目插入到tbl_InventoryPolicyChanges中。

英文:

In my code below, I am getting an error:

> The multi-part identifier "i.Inventoryid" could not be bound.

  1. CREATE TRIGGER trg_UpdateOrderMultiple
  2. ON tbl_InventoryPolicy
  3. AFTER INSERT, UPDATE
  4. AS
  5. BEGIN
  6.     DECLARE @OldOrderMultiple INT
  7.     DECLARE @NewOrderMultiple INT
  8.     DECLARE @Timestamp DATETIME
  10.     SELECT @OldOrderMultiple = d.ordermultiple
  11.     FROM deleted d
  12.     INNER JOIN inserted i ON i.Inventoryid = d.Inventoryid
  14.     SELECT @NewOrderMultiple = i.ordermultiple
  15.     FROM inserted i
  17.     SET @Timestamp = GETDATE()
  18.     IF NOT EXISTS (
  19.             SELECT 1
  20.             FROM tbl_InventoryPolicyChanges
  21.             WHERE Inventoryid = i.Inventoryid
  22.             AND OldOrderMultiple = @OldOrderMultiple
  23.             AND NewOrderMultiple = @NewOrderMultiple
  24.         )
  25.         BEGIN
  26.             INSERT INTO tbl_InventoryPolicyChanges 
  27.                         (InventoryID, OldOrderMultiple, NewOrderMultiple, Timestamp)
  28.             SELECT i.InventoryID, @OldOrderMultiple, 
  29.                     @NewOrderMultiple, @Timestamp
  30.             FROM inserted i
  31.         END
  32. END

I want to avoid inserting duplicate entries into the tbl_InventoryPolicyChanges.

答案1

得分: 2

以下是翻译的代码部分：

  1. 使用 Inserted 和 Deleted 伪表时需要进行连接操作。

  2. 添加 where 子句以筛选掉不需要的更改。

  1. INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
  2. SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
  3. FROM Inserted i
  4. INNER JOIN Deleted d ON d.InventoryId = i.InventoryId
  5. WHERE NOT EXISTS (
  6.     SELECT 1
  7.     FROM tbl_InventoryPolicyChanges
  8.     WHERE Inventoryid = i.Inventoryid
  9.     AND NewOrderMultiple = i.ordermultiple
  10.     AND OldOrderMultiple = d.ordermultiple
  11. );

注意：您的触发器也是插入触发器，但您的逻辑不处理插入操作，因为您假设 Deleted 中会有行。如果还应处理插入操作，那么逻辑需要修改以处理 Deleted 伪表中没有行的情况。

我认为以下代码还处理了INSERT情况，请注意LEFT JOIN 和空值比较：

  1. INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
  2. SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
  3. FROM Inserted i
  4. LEFT JOIN Deleted d ON d.InventoryId = i.InventoryId
  5. WHERE NOT EXISTS (
  6.     SELECT 1
  7.     FROM tbl_InventoryPolicyChanges c
  8.     WHERE c.Inventoryid = i.Inventoryid
  9.     AND c.NewOrderMultiple = i.ordermultiple
  10.     AND (c.OldOrderMultiple = d.ordermultiple OR (d.ordermultiple IS NULL AND c.OldOrderMultiple IS NULL))
  11. );
英文:

You're making the classic mistake many people make when it comes to triggers, and that is to suddenly change from regular SQL set-based operations into some sort of procedural logic. Don't do it, continue to use set-based operations as you would for any other query. I think the following converts your existing logic into set-based logic, but you'll need to validate it yourself.

  1. Use joins with the Inserted and Deleted pseudo tables when required

  2. Add a where clause to filter out unwanted changes

  1. INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
  2. SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
  3. FROM Inserted i
  4. INNER JOIN Deleted d on d.InventoryId = i.InventoryId
  5. WHERE NOT EXISTS (
  6.     SELECT 1
  7.     FROM tbl_InventoryPolicyChanges
  8.     WHERE Inventoryid = i.Inventoryid
  9.     AND NewOrderMultiple = i.ordermultiple
  10.     AND OldOrderMultiple = d.ordermultiple
  11. );

Note: Your trigger is also an insert trigger, but your logic doesn't handle insert because you assume Deleted will have rows in it. If it should also handle inserts then the logic needs to be modified to handle no rows in the Deleted pseudo table.

I think the following also handles the INSERT case, note the LEFT JOIN and the null compare:

  1. INSERT INTO tbl_InventoryPolicyChanges (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
  2. SELECT i.InventoryID, d.ordermultiple, i.ordermultiple, GETDATE()
  3. FROM Inserted i
  4. LEFT JOIN Deleted d on d.InventoryId = i.InventoryId
  5. WHERE NOT EXISTS (
  6.     SELECT 1
  7.     FROM tbl_InventoryPolicyChanges c
  8.     WHERE c.Inventoryid = i.Inventoryid
  9.     AND c.NewOrderMultiple = i.ordermultiple
  10.     AND (c.OldOrderMultiple = d.ordermultiple OR (d.ordermultiple IS NULL AND c.OldOrderMultiple IS NULL))
  11. );

huangapple
  • 本文由 发表于 2023年3月31日 04:46:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/75892868-2.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定