How to update another table using trigger in mysql

When we write a query and save it as a trigger, the query will automatically run when an insert, delete, or update occurs on a specified table. In other words, a trigger is a stored query that is executed automatically based on a specific event, such as an insert, delete, or update.

Triggers cannot return values, but they can read and modify data.

If you want to write a trigger for both before and after an event on a table, you will need to create two separate triggers for each event.

There are six types of triggers available in MySQL.

1. Before update

2. After update

3. Before delete

4. After delete

5. Before insert

6. After insert

Before doing anything, we will create one table and insert some data in created table.

CREATE TABLE `cities` (
 `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `state_id` BIGINT UNSIGNED NOT NULL,
 `city_name` VARCHAR(100) NOT NULL,
 PRIMARY KEY (`id`) USING BTREE
)
ENGINE=INNODB;

INSERT INTO `cities` (`state_id`, `city_name`) VALUES (1, 'Dhanbad');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (2, 'Kozhikode');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (3, 'Pune');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (3, 'Nashik');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (3, 'Mumbai');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (4, 'Ludhiana');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (5, 'Jaipur');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (6, 'Lucknow');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (7, 'Kolkata');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES (6, 'Bulandshahar');

CREATE TABLE `cities_update_log` (
 `log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `id` BIGINT UNSIGNED,
 `state_id` BIGINT UNSIGNED,
 `city_name` VARCHAR(100) ,
 PRIMARY KEY (`log_id`) USING BTREE
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

CREATE TABLE `cities_delete_log` (
 `log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `id` BIGINT UNSIGNED,
 `state_id` BIGINT UNSIGNED,
 `city_name` VARCHAR(100),
 PRIMARY KEY (`log_id`) USING BTREE
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

I assume that you have run that query. Following query for before update cities table trigger.

1. Before Update trigger.

CREATE TRIGGER `cities_update_log_before_update` BEFORE 
UPDATE
  ON `cities` FOR EACH ROW
INSERT INTO
  cities_update_log(`id`, `state_id`, `city_name`)
SELECT
  id,
  `state_id`,
  `city_name`
FROM
  cities
WHERE
  cities.id = OLD.id;

`cities_update_log_before_update` is an example trigger name. You can choose any name you prefer. Then, define the query to execute before or after an update. On line number 3, define the table name where any updates trigger the execution of our query. After that, provide a simple INSERT query. `OLD.id` represents the `id` column of the cities table wehre any changes in value are made.

2. After Update trigger.

CREATE TRIGGER `cities_update_log_after_update` AFTER
UPDATE
  ON `cities` FOR EACH ROW
INSERT INTO
  cities_update_log(`id`, `state_id`, `city_name`)
SELECT
  id,
  `state_id`,
  `city_name`
FROM
  cities
WHERE
  cities.id = new.id;

INSERT and DELETE are similar to UPDATE, so you can easily write INSERT or DELETE triggers as well.

Thank you