I'm executing a function in my NodeJs project that creates a trigger in a table in my database. I've tested the query in azuredatastudio and it works fine, but when i do it from code, it throws what is apparently a syntax error. I vaguely remember having a similar problem a while ago and the solution had to do with an issue with the BEGIN and END keywords and the Tedious driver used by the mssql npm library, but i haven't been able to find the documentation or thread from which i got the solution.
I'm using mssql ^7.2.0
Here's the query
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' -- here is the supposed syntax error
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
And the execution is done via the mssql's query method (request.query(queryStr))
Here's the error log
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: []
}