After insert trigger doesn't work when insert comes from node js app

Question

I have a trigger after insertion. which calls the stored procedure. When I run a direct query, it works well. but when I insert data through node js application. it doesn't even insert data into the main table. There is no error in triggers and stored procedure, since it works with direct insertion request for a table.

dbObject.create(data, function(err, response){ if (err) { //console.log("err", err); res.status(500).jsonp(response) } console.log("response", response); dbObject.find({SnsId: SnsId}, '*' , {}, 0, 0, {}, function(err, resp){ if (err) { //console.log("err", err); res.status(500).jsonp(err) } console.log("response", resp); res.jsonp(resp); }); }); 

start code -

 DELIMITER $$ CREATE TRIGGER `copytoSensorMapTrigger` AFTER INSERT ON test4.`SENSORS` FOR EACH ROW BEGIN CALL copyToSensorMap(NEW.`SnsId`,NEW.`SnsState`); END; 

The stored procedure code is

 DELIMITER $$ CREATE PROCEDURE copyToSensorMap(IN snsid bigint(20),IN sns_state varchar(128)) BEGIN DECLARE color varchar(128); INSERT INTO test3.`SENSOR_MAP` (`SENSOR_ID`, `SENSOR_NAME`, `USER_ID`, `HOST_ID`, `SENSOR_STATE`) SELECT `SnsId`, `SnsName`,`SnsOwner`,`HostId`,`SnsState` FROM test4.`SENSORS` where test4.`SENSORS`.`SnsId`=snsid; IF ( sns_state ='Unassigned') THEN set color = 'Grey'; ELSEIF ( sns_state ='Running') THEN set color = 'Green'; END IF; update test3.`SENSOR_MAP` set test3.`SENSOR_MAP`.`SENSOR_COLOR` = color where test3.`SENSOR_MAP`.`SENSOR_ID` = snsid; UPDATE test3.`SENSOR_MAP` INNER JOIN test4.`HOSTS` ON (test3.`SENSOR_MAP`.`HOST_ID` = test4.`HOSTS`.id) SET test3.`SENSOR_MAP`.HOST_NAME = test4.HOSTS.Name, test3.`SENSOR_MAP`.LATITUDE = test4.HOSTS.GpsLatitude, test3.`SENSOR_MAP`.LONGITUDE = test4.HOSTS.GpsLongitude; UPDATE test3.`SENSOR_MAP` INNER JOIN test4.`users` ON (test3.`SENSOR_MAP`.`USER_ID` = test4.`users`.id) SET test3.`SENSOR_MAP`.USER_NAME = test4.users.user; UPDATE test3.`SENSOR_MAP` c INNER JOIN ( select a.`SnsId`,b.id,b.ServiceName,b.ServiceType from test4.sensors as a join test4.SESSIONS as b on a.SessionId =b.id where a.`SnsId` =snsid group by `SnsId` ) d ON c.SENSOR_ID = d.`SnsId` SET c.SESSION_NAME = d.ServiceName, c.SESSION_TYPE = d.ServiceType; END $$ 

I am using a Mysql database and the storage system is InnoDB. I checked the sql query. It is formed using the SET Keyword. Mysql inserts a query with keyword support after inserting a stored procedure.

The problem is resolved now. that was the name of the table in all caps in Mysql, and I wrote lowercase. The names of the names were "user" and "sensor". it is now resolved.

Answer 1

I would also recommend using return in places where you check for errors, otherwise the code will continue and not stop in case of errors.

Try using this code:

 dbObject.create(data, function(err, response){ if (err) { //console.log("err", err); return res.status(500).jsonp(response) } console.log("response", response); dbObject.find({SnsId: SnsId}, '*' , {}, 0, 0, {}, function(err, resp){ if (err) { //console.log("err", err); return res.status(500).jsonp(err) } console.log("response", resp); res.jsonp(resp); }); });