如何使用mysqljs包为nodejs创建触发器？

Question

我正在尝试为数据库创建一个触发器，使用nodejs的mysql包。但我说错了

您的SQL语法有错误；请检查与您的MySQL服务器版本对应的手册，以便在第1行的“Leave\”插入后使用接近\‘分隔符$$创建触发器'NotificationTrigger’的正确语法。

var createNotificationTrigger = {
  query:

    'DELIMITER $$\
    CREATE TRIGGER `NotificationTrigger` \
    After INSERT  ON `Leave` \
    FOR EACH ROW \
    BEGIN \
    Declare fullname  varchar(255); \
    Declare lastname  varchar(255); \
    Declare requestedbyid  int; \
    Declare insertstring varchar(255); \
    Set fullname = ( select Employee.FirstName from Employee where Employee.ID = NEW.RequestedByID); \
    Set lastname =    ( select Employee.LastName from Employee where Employee.ID = NEW.RequestedByID); \
    set fullname = (select  Concat(fullname," ",lastname)); \
    set requestedbyid= (select  New.RequestedByID); \
    set  insertstring = (select Concat("New Leave request from ",fullname , " employee id : ",requestedbyid)); \
    INSERT INTO `Notification` \
    (`Title`,`SeenBy`,`SentByID`,`NotificationTypeID`) \
    VALUES(insertstring, requestedbyid, requestedbyid,2) ; \
    END$$ \
    DELIMITER ;'

};

这就是我的触发器查询的样子。我正在使用以下方法执行此操作：

connection.query(createNotificationTrigger.query,[],(err)=>{//something here});

这应该会在我的数据库中创建一个触发器，但是会抛出上面提到的错误。该查询在mysql工作台中运行良好。

Answer 1

根据this answer，您应该能够通过删除DELIMITER语句来创建触发器，而不会出现错误。我用自己的触发器使用自己的模式进行了测试，这是可行的。

因此，您的代码应该修改为以下内容以使其工作：

var createNotificationTrigger = {
  query:
    'CREATE TRIGGER `NotificationTrigger` \
    After INSERT  ON `Leave` \
    FOR EACH ROW \
    BEGIN \
    Declare fullname  varchar(255); \
    Declare lastname  varchar(255); \
    Declare requestedbyid  int; \
    Declare insertstring varchar(255); \
    Set fullname = ( select Employee.FirstName from Employee where Employee.ID = NEW.RequestedByID); \
    Set lastname =    ( select Employee.LastName from Employee where Employee.ID = NEW.RequestedByID); \
    set fullname = (select  Concat(fullname," ",lastname)); \
    set requestedbyid= (select  New.RequestedByID); \
    set  insertstring = (select Concat("New Leave request from ",fullname , " employee id : ",requestedbyid)); \
    INSERT INTO `Notification` \
    (`Title`,`SeenBy`,`SentByID`,`NotificationTypeID`) \
    VALUES(insertstring, requestedbyid, requestedbyid,2) ; \
    END'
};