How to create triggers using mysqljs package for nodejs?

Question

I am trying to create a trigger for a database, using mysql package for nodejs. But I get an error saying

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'DELIMITER $$ CREATE TRIGGER 'NotificationTrigger' After INSERT ON 'Leave\' at line 1'.

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 ;'

};

This is what my trigger query looks like. I am executing this using :

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

This should create a trigger in my database but instead the error mentioned above is thrown. The query works fine inside mysql workbench.

score 1 · Answer 1 · answered Apr 02 '20 at 01:11

According to this answer, you should be able to create the trigger without error by removing the DELIMITER statement. I have tested using my own schema with my own trigger and that works.

So your code should revise to the following to make it work:

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'
};

How to create triggers using mysqljs package for nodejs?

1 Answers1