Mysql Trigger After Delete

Trigger in Mysql fired trigger automatically after you perform Delete Operation on Table.

Understand with Example

The Tutorial illustrate an example from 'Trigger After Delete' in Mysql. To understand this example, we create a table 'Employee'. The create table 'employee' is created with different field name and data type respectively.

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

The insert into add the records or rows into the 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)

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

View table:-

To view table employee, we use select keyword that show you the entire records from table 'employee'.

mysql> select * from employee;

Output:-

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

Create table:-

Now we include another table 'Employee_log'. The create table construct another 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
    ->      );
Query OK, 0 rows affected (0.05 sec)

View table:-

Again, we use same select Query to return the records from a table employee_log.

mysql> select * from employee_log;

Output:-

Empty set (0.00 sec)

Create Trigger:-

Drop Trigger is used to delete trigger Employee_Trigger from database, in case if there is any trigger existing on this name. The 'Employee_Triger'  fired trigger after you perform a delete operation on table 'employee'. The deleted records from table 'employee' copy into a table 'employee_log'.

drop trigger if exists Employee_Trigger;

delimiter $$
CREATE TRIGGER Employee_Trigger
AFTER delete ON employee
FOR EACH ROW
BEGIN
insert into employee_log values(old.id,old.first_name,
old.last_name,old.start_date,old.end_date,
old.city,old.description,curtime());
 END$$
delimiter ;

Query to delete record from employee table:-

We run a delete query  in table employee where id is '4'. 

mysql> delete from employee where id =4;

Table that has been modified after delete 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  | 15:42:38    |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
1 row in set (0.00 sec)