Insert or update a row using MySQL Trigger

Question

I have two MySQL tables: sms and consumption. The sms table contain all transactions while the consumption table contain the sum of sms consumed.

My goal is to be able to now the actual consumption for a day, so I need just to check directly on consumption table. For it, I'm trying to create a trigger that will verify:

if for day 1 there is no row in consumption table, then insert a new row
if there is already a row for day 1, then update it

Here my code that doesn't work.

CREATE TRIGGER tg_new_sms_sent
AFTER INSERT ON sms
FOR EACH ROW
    SET @k = (SELECT id FROM consumption WHERE period = NEW.simple_date);
    CASE
        WHEN ISNULL(@k) THEN
            INSERT INTO consumption (system_id, period, credit, sms)
            VALUES (NEW.system_id, NEW.simple_date, NEW.used_credit, NEW.sms_count);
        ELSE
            UPDATE consumption SET credit = credit + New.used_credit, sms = sms + NEW.sms_count WHERE (system_id = NEW.system_id) AND (period = NEW.simple_date);
    END CASE

How can I do?

What is not working? Your trigger or you could not add a trigger? — Manoj-kr
– Manoj-kr, Commented Jul 17, 2017 at 10:41
Try to put you script between : delimiter $$ // your script // $$ — Manoj-kr
– Manoj-kr, Commented Jul 17, 2017 at 10:50

Keyur Panchal · Accepted Answer · 2017-07-17 10:49:32Z

1

Try the code below.

DELIMITER $$
CREATE TRIGGER tg_new_sms_sent
AFTER INSERT ON sms
FOR EACH ROW
  BEGIN
    DECLARE num_rows INT;

    SELECT COUNT(id) INTO num_rows FROM consumption
    WHERE system_id = NEW.system_id AND period = NEW.simple_date;

    IF num_rows = 0 THEN
        INSERT INTO consumption (system_id, period, credit, sms)
        VALUES (NEW.system_id, NEW.simple_date, NEW.used_credit, NEW.sms_count);
    ELSE
        UPDATE consumption
        SET credit = credit + NEW.used_credit, sms = sms + NEW.sms_count
        WHERE system_id = NEW.system_id AND period = NEW.simple_date;
    END IF;

  END$$
DELIMITER ;

answered Jul 17, 2017 at 10:49

Keyur Panchal

1,4121 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Keyur Panchal Over a year ago

Please note that you must use system_id = NEW.system_id in your WHERE condition of SELECT query. Otherwise with the solution that you have selected, it is possible that you will get count > 0 but your UPDATE will fail if record with that id is not present in consumption.

nekiala Over a year ago

I understand that if I add system_id = NEW.system_id in my WHERE clause, it will not give me TOO_MANY_RECORDS exception. Thank you!

halfer · Accepted Answer · 2018-05-01 21:30:11Z

1

DELIMITER $$
CREATE TRIGGER tg_new_sms_sent AFTER INSERT ON sms
FOR EACH ROW
BEGIN
DECLARE VAR_CNT INT;
      SELECT id INTO VAR_CNT FROM consumption WHERE period = NEW.simple_date AND system_id = NEW.system_id;

      IF VAR_CNT = 0 THEN  
        INSERT INTO consumption (system_id, period, credit, sms)
        VALUES (NEW.system_id, NEW.simple_date, NEW.used_credit, NEW.sms_count);
      ELSE
        UPDATE consumption SET credit = credit + New.used_credit, sms = sms + NEW.sms_count WHERE (system_id = NEW.system_id) AND (period = NEW.simple_date);
      END IF ;

END;$$

DELIMITER ;

Try above query.

edited May 1, 2018 at 21:30

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Jul 17, 2017 at 10:40

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

3 Comments

Sagar Gangwal Over a year ago

@nekiala Why you did not accept my answer?? Even though i had given it before 28 mins ago..

nekiala Over a year ago

Hi @Sagar, your code worked well. I just noticed that when a new row was inserted in sms table, the trigger inserted a new row in consumption table for the same period instead of updating the existing row.

nekiala Over a year ago

I just reviewed your answer, the mistake is here: IF VAR_CNT != 0 THEN INSERT. It must have been IF VAR_CNT = 0 THEN INSERT. Because my goal was : if there is no row for day 1, then insert, if not, then update.

Collectives™ on Stack Overflow

Insert or update a row using MySQL Trigger

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related