sql - 无法绑定(bind)多部分标识符 "INSERTED. "

Question

我想写一个触发器:如果表中存在记录，则更新另一个表，如果不存在则插入该表创建此触发器时出错:

CREATE TRIGGER TR_update_laststatus_on_membersservices ON [dbo].[activemembers] AFTER 
INSERT AS
BEGIN
IF EXISTS    
   (
   SELECT 1   
   FROM [membersservices] 
WHERE [membersservices].[mobilenumber] = INSERTED.[mobilenumber]
    AND [membersservices].[sid] = INSERTED.[sid]    
)    
BEGIN
  UPDATE membersservices  
  SET  membersservices.[last_status] = INSERTED.enable
  WHERE membersservices.mobilenumber=INSERTED.mobilenumber and membersservices.[sid]=INSERTED.[sid]
END
ELSE
    INSERT INTO membersservices VALUES ( INSERTED.[mobilenumber] ,INSERTED.[sid] ,INSERTED.[enable] , INSERTED.[dt] ); 
end

Answer 1

INSERTED 是一个(伪)表，因此它必须出现在 FROM 子句中，每次您使用它时:

SELECT 1   
   FROM [membersservices] 
   JOIN INSERTED on ...  <--- you're missing this
WHERE [membersservices].[mobilenumber] = INSERTED.[mobilenumber]
    AND [membersservices].[sid] = INSERTED.[sid]  


UPDATE membersservices  
 SET  membersservices.[last_status] = INSERTED.enable
FROM membersservices     
JOIN INSERTED ON ...  <-- you're missing this


INSERT INTO membersservices (...)
SELECT ...
FROM INSERTED ...  <-- you're missing this

除此之外，您的逻辑在并发下是不正确的。您应该使用单个 MERGE执行更新和插入的语句。