Mysql Trigger after Update

Mysql Trigger after  Update fired automatically after we perform an update query on table.

Understand with Example

The Tutorial understand an example from 'Mysql Trigger after Update'. To grasp 'Mysql Trigger after Update', we  create a table 'Employee' with field attribute and data type respectively.

Create table:-

 insert into Employee values 
(01,'Girish','Tewari','20081225',  '20100625','Nainital','Programmer');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Employee values 
(02,'Komal','Choudhry','20071122', '20100421','Meerut','Programmer');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Employee values 
(03,'Mahendra','Singh','20061012',  '20070512','Lucknow','Programmer');
Query OK, 1 row affected (0.02 sec)
>mysql> CREATE TABLE Employee(
    ->          id            int,
    ->          first_name    VARCHAR(30),
    ->          last_name     VARCHAR(15),
    ->          start_date    DATE,
    ->          end_date      DATE,
    ->          city          VARCHAR(10),
    ->          description   VARCHAR(15)
    ->      );
Query OK, 0 rows affected (0.05 sec)

Insert data into table:-

The insert into insert the records or rows into table 'Employee'.

mysql> insert into Employee values 
(01,'Girish','Tewari','20081225',  '20100625','Nainital','Programmer');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Employee values 
(02,'Komal','Choudhry','20071122', '20100421','Meerut','Programmer');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Employee values 
(03,'Mahendra','Singh','20061012',  '20070512','Lucknow','Programmer');
Query OK, 1 row affected (0.02 sec)

View table:-

mysql> select * from employee;

Output:-

+------+------------+-----------+------------+------------+----------+-------------+
| id   | first_name | last_name | start_date | end_date   | city     | description |
+------+------------+-----------+------------+------------+----------+-------------+
|    1 | Girish     | Tewari    | 2008-12-25 | 2010-06-25 | Nainital | Programmer  |
|    2 | Komal      | Choudhry  | 2007-11-22 | 2010-04-21 | Meerut   | Programmer  |
|    3 | Mahendra   | Singh     | 2006-10-12 | 2007-05-12 | Lucknow  | Programmer  |
+------+------------+-----------+------------+------------+----------+-------------+
3 rows in set (0.00 sec)

Create table:-

Now Create another table 'Employee_log' with field name and data type respectively.

mysql> CREATE TABLE Employee_log(
    ->          user_id       VARCHAR(15),
    ->          description   VARCHAR(100)
    ->      );

View table:-

mysql> select * from employee_log;

Output:-

Empty set (0.00 sec)

Create Trigger:-

The Create Trigger create a Employee_Trigger on table 'employee'. The Trigger 'Employee_Trigger' fired automatically after update operation is performed on table 'employee'. Further,the update records is inserted on table 'Employee_log'.

mysql> delimiter $$
mysql> CREATE TRIGGER Employee_Trigger
    -> AFTER UPDATE ON employee
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT into Employee_log
    -> (user_id, description)VALUES (user(),
    -> CONCAT('Id with ',NEW.id,' is modified ',
    -> ' from ',OLD.start_date, ' to ', NEW.start_date));
    ->  END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

Query to update employee table:-

mysql> update employee set start_date='20061231';

Table that has been modified after update query executes is Employee_log:-

Query to view Employee_log table:-

mysql> select * from employee_log;

Output:-

+----------------+------------------------------------------------------+
| user_id        | description                                          |
+----------------+------------------------------------------------------+
| root@localhost | Id with 1 is modified  from 2008-12-24 to 2006-12-31 |
| root@localhost | Id with 2 is modified  from 2008-12-24 to 2006-12-31 |
| root@localhost | Id with 3 is modified  from 2008-12-24 to 2006-12-31 |
+----------------+------------------------------------------------------+
3 rows in set (0.00 sec)