SQL Server 创建触发器：关键字“IF”附近的语法不正确

debugcn 发表于 Dev

阿根廷

我正在尝试在 SQL Server 中转换 db2 触发器。我已经更改了一些内容作为 NEWDOC 和 OLDDOC 引用并删除了“THEN”关键字。

我仍然收到此错误：

Incorrect syntax near ';'.
Incorrect syntax near the keyword 'IF'.

SQL是：

-- <ScriptOptions statementTerminator="!" />
CREATE TRIGGER DOCUMENTS_UPDATE ON DOCUMENTS_VIEW
INSTEAD OF UPDATE 
AS 
BEGIN
  IF (select user_id from inserted) is null
    UPDATE DOCUMENTS 
    SET    id = (select id from inserted), 
           user_id = (SELECT id from users 
                      where username = (select username from inserted)
                     ), 
           code = (select code from inserted), 
           description = (select description from inserted), 
           type = (select type from inserted), 
           expiry = (select expiry from inserted), 
           parent_code = (select parent_code from inserted), 
           attributes = (select attributes from inserted), 
           acquired = (select acquired from inserted) 
    WHERE id = (select id from inserted);
  ELSE 
    UPDATE DOCUMENTS 
    SET    id = (select id from inserted), 
           user_id = (select user_id from inserted), 
           code = (select code from inserted), 
           description = (select description from inserted), 
           type = (select type from inserted), 
           expiry = (select expiry from inserted), 
           parent_code = (select parent_code from inserted), 
           attributes = (select attributes from inserted), 
           acquired = (select acquired from inserted) 
    WHERE id = (select id from inserted);
  END IF;

  UPDATE DOCUMENT_STATES 
  SET    document_id = (select id from inserted), 
         state = (select state from inserted), 
         date = (select date from inserted), 
         timestamp = (select timestamp from inserted) 
  WHERE document_id = (select id from inserted);

  IF NEWDOC.type = 'client_order' 
    UPDATE DOCUMENTS 
    SET    parent_code = (select code from inserted) 
    WHERE code = (select parent_code from inserted);
END IF;
END!

戈登·利诺夫

您的语法问题很容易解决。正如 GandRalph 指出的那样，END IFSQL Server 中没有。

更重要的是，你没有inserted正确使用。它可以包含多行——受 DML 语句影响的所有行。发生这种情况时，您的查询将因错误而严重失败。

第一个if可以替换为单个update. 它看起来像这样：

update d
    set  user_id = coalesce(u.id, i.userid),
         code = i.code, 
         description = i.description, 
         type = i.type, 
         expiry = i.expiry, 
         parent_code = i.parent_code, 
         attributes = i.attributes, 
         acquired = i.acquired 
    from documents d join
         inserted i
         on d.id = i.id left join
         users u
         on u.username = i.username

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。