1

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

Improve this question
4
  • a) You cannot update the table which the trigger is defined on; b) SET ALERT = NEW.LTP; - what is ALERT in this place? Commented Sep 1, 2021 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 Commented Sep 1, 2021 at 15:34
  • See the following answer to the duplicate question: stackoverflow.com/a/29559195/5389997 Commented Sep 1, 2021 at 15:35
  • Without table alias your ALERT is unknown object in this context. Commented Sep 1, 2021 at 15:35

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @nbk. It worked like charm. Thank you again
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;
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.