Create Trigger : update table column by inserting data into another table rows

Question

Please Help me to create triggers, I have 2 tables of

data
+---------+------------+-------+-------+
| id | info_id | rate  | qty   | total |
+----+---------+------------+----------+
| 80 |    10   | 150   |   5   |  750  |
+----+---------+-------+-------+-------+
| 81 |    10   | 50    |   5   |  250  |
+--------------+-------+-------+-------+

info
+---------+---------------+------------+
| id  |  name  |  gtotal  |  dated     |
+-----+--------+----------+------------+
| 10  |  Hari  |     NULL | 2021-05-15 |
+---------+------------+---------------+

I want to create a trigger through phpmyadmin, as soon as data will be inserted, then info.gtotal will be updated by adding data.total from matched info_id from table name- data.

what will be the trigger if update on data happens too. I just want to create both triggers.

I am new with such, please help me. Any help is accepted.

Prakaash · Accepted Answer · 2021-05-15 13:01:24Z

2

You need to create two triggers one for insert and another for update.

CREATE TRIGGER `insert_trigger`
AFTER INSERT ON `data`
FOR EACH ROW

UPDATE info
SET gtotal = gtotal + new.total
WHERE id = new.info_id;

Here new.info_id will have the info_id value of newly insert record

CREATE TRIGGER `update_trigger`
AFTER UPDATE ON `data`
FOR EACH ROW

UPDATE info
SET gtotal = (gtotal - old.total) + new.total
WHERE id = new.info_id;

Here old.total will have the total value of record before updating. new.total is the total value after updation.

Since you haven't conveyed what to do after update I have added the logic of subtracting the total from old total and added the new total value. Change it as per your requirement.

You can also create these triggers using GUI in phpmyadmin. You have to select the triggers in the menu bar and add the trigger definition.

edited May 15, 2021 at 13:01

answered May 15, 2021 at 10:31

Prakaash

897 bronze badges

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

1 Comment

Umesh Gaire Over a year ago

Thank You so much for your detailed reply. I loved it. firstly it is not worked but as soon I simply added new.total which worked. Final code became to UPDATE info SET gtotal = gtotal + new.total WHERE id = new.info_id; Which worked.

Collectives™ on Stack Overflow

Create Trigger : update table column by inserting data into another table rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related