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