Mysql Trigger After Insert

Mysql Trigger After Insert fired the trigger after you perform an insert a records or rows to the table.

Understand with Example

The Tutorial illustrate an example from Mysql Trigger After Insert. To understand this, we create a table 'Employee'. The create table is used to construct a table 'Employee'.

Create table:-

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:-

Insert into add the records or rows into a 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:-

To view a table employee we use select query that return the records from a table 'employee'.

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:-

The create table construct a table Employee_log.

mysql> CREATE TABLE Employee_log(
    ->          id int,
    ->   first_name varchar(50),
    ->  last_name varchar(50),
    ->  start_date date,
    ->  end_date date,
    ->  city varchar(50),
    ->  description varchar(50),
    ->  Lasinserted Time
    ->      );

View table:-

mysql> select * from employee_log;

Output:-

Empty set (0.02 sec)

Create Trigger:-

The Create Trigger create a trigger 'Employee_Trigger' on table 'employee'. The 'After insert trigger'  fired the trigger after you performed a insert operation on table.

mysql> delimiter $$
mysql> CREATE TRIGGER Employee_Trigger
    -> AFTER insert ON employee
    -> FOR EACH ROW
    -> BEGIN
    -> insert into employee_log values(new.id,new.first_name,
    -> new.last_name,new.start_date,new.end_date,
    -> new.city,new.description,curtime());
    ->  END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

Query to insert into employee table:-

The insert into add the records or rows into the table 'employee'.

mysql> insert into employee values (4,'Amit','Kumar','20081225','20101203','Lucknow','Programmer');

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

Query to view Employee_log table:-

mysql> select * from employee_log;

Output:-

+------+------------+-----------+------------+------------+---------+-------------+-------------+
| id   | first_name | last_name | start_date | end_date   | city    | description | Lasinserted |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
|    4 | Amit       | Kumar     | 2008-12-25 | 2010-12-03 | Lucknow | Programmer  | 14:55:31    |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
1 row in set (0.00 sec)