CODEWITHSUNDEEP
mysqltriggersmariadb
nekiala
nekiala

Reputation: 480

Insert or update a row using MySQL Trigger

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:

  1. if for day 1 there is no row in consumption table, then insert a new row
  2. 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?

Upvotes: 0

Views: 58

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

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.

Upvotes: 1

Keyur Panchal
Keyur Panchal

Reputation: 1402

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 ;

Upvotes: 1

Related Questions