Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Trigger Mysql Trigger after Update

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Trigger after Update

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

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

If you enjoyed this post then why not add us on Google+? Add us to your Circles



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: June 12, 2009

Related Tutorials

Discuss: Mysql Trigger after Update   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0
DMCA.com