Home Sql Trigger Mysql Trigger After Delete
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Trigger After Delete

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

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)
Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: September 22, 2006

Ask Questions?    Discuss: Mysql Trigger After Delete   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Anuj
December 2, 2011
Query regarding Trigger

Your Code is not properly work in mysql version() | ---------------------+ 4.1.22-community-nt | ---------------------+ So Plz provide solution ASAP !! Thanks
Walter
April 11, 2012
MySQL trigger after delete

Very clear and nice explained.
danish
June 12, 2012
Triggers

trigerrs are defined very breifly and in a very nice way thanx for it
Jose
August 8, 2012
Excelent!

Just what I was looking for.
sidney suderio
September 23, 2012
gostei muito da trigger, sou iniciante.

antes de deletar o registro ele pede confirmação, gostei muito quanto houverem mais tutoriais como este gostaria de recebe-los
DMCA.com