如何在插入时使用触发器更新，但不使用插入值

Question

我正在尝试编辑这个命令:

CREATE TRIGGER [dbo].[TG_DATA_U] 
ON [dbo].[TABLE2]
FOR UPDATE
AS
UPDATE TABLE1 
SET PRICE = (SELECT PRICE FROM INSERTED)
WHERE ID = (SELECT ID FROM INSERTED)

结果需要:表1

ID    PRICE
------------
1     20
2     11

表2

IDTABLE2   ID    PRICE    IDGROUP
---------------------------------
1      1      20        1
2      2      11        1
3      2      15        2

如果我更新价格"15"，我需要让触发器总是更新TABLE 1的价格，并且只更新IDGROUP = 1的价格。将只更新TABLE 1与价格"11"

Answer 1

我认为这就是你想要的:

UPDATE TABLE1
SET PRICE = (SELECT PRICE FROM TABLE1 WHERE ID = 11)
WHERE ID IN (SELECT ID FROM INSERTED);

Answer 2

您的触发器是有缺陷的:它假设inserted只包含一行。可以包含0个或多个

你的触发器应该是这样的

CREATE OR ALTER TRIGGER [dbo].[TG_DATA_U] ON [dbo].[TABLE2]
FOR UPDATE
AS
SET NOCOUNT ON;
UPDATE TABLE1
SET PRICE = i.PRICE
FROM TABLE1 t1
JOIN inserted i ON i.ID = t1.ID
WHERE t1.IDGROUP = 1

Answer 3

找到了一个更简单的方法，只使用

"if (SELECT ID FROM insert) = 1">

CREATE TRIGGER [dbo].[TG_DATA_U] 
ON [dbo].[TABLE2]
FOR UPDATE
AS
if (SELECT IDTABELA FROM INSERTED) = 1
UPDATE TABLE1 
SET PRICE = (SELECT PRICE FROM INSERTED)
WHERE ID = (SELECT ID FROM INSERTED)