MySQL Trigger error while updating in backend

Question

Hi I need a help in the below issue. I have table where data being updated every minute. I have a trigger set on this table.

    CREATE DEFINER=`root`@`localhost` TRIGGER `gsdatatabs_AFTER_UPDATE` AFTER UPDATE ON 
      `gsdatatabs` FOR EACH ROW BEGIN
       IF NEW.CAMARILLA = 'B' or NEW.CAMARILLA = 'S' then
            UPDATE gsdatatabs SET ALERT = NEW.LTP;
       END IF;
    END

Below is my table structure

Columns:
SCRIP varchar(45) 
LTP float 
OHL varchar(45) 
ORB15 varchar(45) 
ORB30 varchar(45) 
PRB varchar(45) 
CAMARILLA varchar(45) 
ALERT float

I am trying to update ALERT column with value from LTP when the CAMARILLA value is 'B' or 'S'. In the backend the data for CAMARILLA column gets updated every minute.

Currently while updating in the backend getting error.

Error: Can't update table 'gsdatatabs' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
    at Packet.asError (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\packets\packet.js:722:17)
    at Query.execute (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\commands\command.js:28:26)
    at Connection.handlePacket (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\connection.js:456:32)
    at PacketParser.onPacket (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\connection.js:85:12)
    at PacketParser.executeStart (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\Users\sprasadswain\Documents\googleSheet\AutoTrader\Server\node_modules\mysql2\lib\connection.js:92:25)
    at Socket.emit (events.js:315:20)

Kindly guide

a) You cannot update the table which the trigger is defined on; b) `SET ALERT = NEW.LTP;` - what is `ALERT` in this place? — Akina, Sep 01 '21 at 15:30
ALERT will be initially empty. when the value of CAMARILLA column changes to B or S I want to set it with value of LTP — Siba Swain, Sep 01 '21 at 15:34
See the following answer to the duplicate question: https://stackoverflow.com/a/29559195/5389997 — Shadow, Sep 01 '21 at 15:35
Without table alias your `ALERT` is unknown object in this context. — Akina, Sep 01 '21 at 15:35

score 1 · Accepted Answer · answered Sep 01 '21 at 15:33

1

change it to before update

CREATE DEFINER=`root`@`localhost` TRIGGER `gsdatatabs_AFTER_UPDATE` BEFORE UPDATE ON 
  `gsdatatabs` FOR EACH ROW BEGIN
   IF NEW.CAMARILLA = 'B' or NEW.CAMARILLA = 'S' then
        SET NEW.ALERT = NEW.LTP;
   END IF;
END

answered Sep 01 '21 at 15:33

nbk

45,398
8
30
47

Thanks @nbk. It worked like charm. Thank you again – Siba Swain Sep 01 '21 at 16:22

score 0 · Answer 2 · answered Sep 01 '21 at 15:33

0

It seems that you need in

CREATE DEFINER=`root`@`localhost` TRIGGER `gsdatatabs_BEFORE_UPDATE` 
BEFORE UPDATE ON `gsdatatabs` 
FOR EACH ROW 
SET NEW.alert = CASE WHEN NEW.camarilla IN ('B', 'S')
                     -- AND NEW.alert IS NULL
                     THEN NEW.ltp
                     ELSE NEW.alert
                     END;

answered Sep 01 '21 at 15:33

Akina

39,301
5
14
25

Thanks for your Response. Still the same issue coming while the backend script runs to update the table. Backend I am not touching the ALERT column. I want it to populated dynamically "update gsdatatabs SET LTP = '3300.5', OHL = 'NA', ORB15 = 'BREAKOUT', ORB30 = 'BREAKOUT', PRB = 'BREAKUP', CAMARILLA = 'B' where SCRIP = 'ASIANPAINT'" This query runs every 1 min for Various SCRIP. – Siba Swain Sep 01 '21 at 16:07

MySQL Trigger error while updating in backend

2 Answers2