Node.js中mssql执行触发器SQL报错，Azure Data Studio可正常运行

问题场景 #

在Node.js项目中使用mssql ^7.2.0库执行创建数据库触发器的SQL语句，该语句在Azure Data Studio中可正常运行，但通过request.query()方法执行时触发语法错误，提示Incorrect syntax near '01'。曾遇到类似问题，怀疑与mssql依赖的Tedious驱动对BEGIN/END关键字的处理有关，但遗忘了解决方案。

执行的SQL语句 #

CREATE TRIGGER [dbo].[SET_PRODUCT_UPDATE_SAEXIS] ON [dbo].[SAEXIS] AFTER Insert,UPDATE AS

DECLARE @sku VARCHAR(15), @CodUbic VARCHAR(10),  @name VARCHAR(40), @description VARCHAR(40), @categories INT, @regular_price VARCHAR(30), @sale_price VARCHAR(30), @manage_stock INT, @stock_quantity DECIMAL(28,4)

SELECT @sku = Codprod,@Codubic=Codubic FROM inserted
IF @Codubic='01' -- 报错指向此处
BEGIN
    IF EXISTS (Select SKU from TBL_WC_WEB_PRODUCTS_OPERATIONS where operation='P')
    BEGIN
        SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
        FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
        DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
        INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
        VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'C')
    END
    ELSE
    BEGIN
        SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
        FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
        DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
        INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
        VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'U')
    END
END

错误日志 #

RequestError: Incorrect syntax near '01'.
    at handleError (/home/koe/Escritorio/code/work/wc_saint/node_modules/mssql/lib/tedious/request.js:384:15)
    at Connection.emit (node:events:527:28)
    at Connection.emit (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1065:18)
    at Parser.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1170:12)
    at Parser.emit (node:events:527:28)
    at Readable.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at Readable.emit (node:events:527:28)
    at addChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:298:12)
    at readableAddChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:280:11)
    at Readable.push (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:241:10) {
  code: 'EREQUEST',
  originalError: Error: Incorrect syntax near '01'.
      at handleError (/home/koe/Escritorio/code/work/wc_saint/node_modules/mssql/lib/tedious/request.js:382:19)
      at Connection.emit (node:events:527:28)
      at Connection.emit (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1065:18)
      at Parser.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1170:12)
      at Parser.emit (node:events:527:28)
      at Readable.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
      at Readable.emit (node:events:527:28)
      at addChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:298:12)
      at readableAddChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:280:11)
      at Readable.push (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:241:10) {
    info: ErrorMessageToken {
      name: 'ERROR',
      event: 'errorMessage',
      number: 102,
      state: 1,
      class: 15,
      message: "Incorrect syntax near '01'.",
      serverName: 'garuda',
      procName: 'SET_PRODUCT_CREATE',
      lineNumber: 13
    }
  },
  number: 102,
  lineNumber: 13,
  state: 1,
  class: 15,
  serverName: 'garuda',
  procName: 'SET_PRODUCT_CREATE',
  precedingErrors: []
}

解决方案 #

该问题确实源于Tedious驱动对多行SQL脚本的解析逻辑，尤其是包含BEGIN/END代码块的语句。以下是几种可行的解决方式：

方法1：启用批处理模式执行 #

调用request.query()时添加{ batch: true }选项，让驱动以批处理模式执行整个SQL脚本，避免逐行解析导致的语法错误：

await request.query(sqlStatement, { batch: true });

方法2：调整SQL语句换行格式 #

将SQL语句中的换行符替换为空格，确保IF与BEGIN处于同一逻辑行（此方式会降低SQL可读性，不推荐）：

CREATE TRIGGER [dbo].[SET_PRODUCT_UPDATE_SAEXIS] ON [dbo].[SAEXIS] AFTER Insert,UPDATE AS DECLARE @sku VARCHAR(15), @CodUbic VARCHAR(10),  @name VARCHAR(40), @description VARCHAR(40), @categories INT, @regular_price VARCHAR(30), @sale_price VARCHAR(30), @manage_stock INT, @stock_quantity DECIMAL(28,4) SELECT @sku = Codprod,@Codubic=Codubic FROM inserted IF @Codubic='01' BEGIN IF EXISTS (Select SKU from TBL_WC_WEB_PRODUCTS_OPERATIONS where operation='P') BEGIN SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation) VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'C') END ELSE BEGIN SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation) VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'U') END END

方法3：用存储过程封装触发器创建逻辑 #

先创建存储过程封装触发器的创建逻辑，再通过mssql调用存储过程，规避驱动解析多行SQL的问题：

CREATE PROCEDURE [dbo].[CreateProductUpdateTrigger]
AS
BEGIN
    SET NOCOUNT ON;
    -- 先删除已存在的同名触发器
    IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'SET_PRODUCT_UPDATE_SAEXIS')
        DROP TRIGGER [dbo].[SET_PRODUCT_UPDATE_SAEXIS];
    
    -- 创建触发器
    CREATE TRIGGER [dbo].[SET_PRODUCT_UPDATE_SAEXIS] ON [dbo].[SAEXIS] AFTER Insert,UPDATE AS
    DECLARE @sku VARCHAR(15), @CodUbic VARCHAR(10),  @name VARCHAR(40), @description VARCHAR(40), @categories INT, @regular_price VARCHAR(30), @sale_price VARCHAR(30), @manage_stock INT, @stock_quantity DECIMAL(28,4)

    SELECT @sku = Codprod,@Codubic=Codubic FROM inserted
    IF @Codubic='01'
    BEGIN
        IF EXISTS (Select SKU from TBL_WC_WEB_PRODUCTS_OPERATIONS where operation='P')
        BEGIN
            SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
            FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
            DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
            INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
            VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'C')
        END
        ELSE
        BEGIN
            SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
            FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
            DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
            INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
            VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'U')
        END
    END
END

在Node.js中调用存储过程：

await request.execute('CreateProductUpdateTrigger');

优先推荐方法1，操作简单且不会破坏SQL语句的可读性。

内容的提问来源于stack exchange，提问作者koe255