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:-
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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
