Mysql Trigger After Insert
Mysql Trigger After Insert fired the trigger after you perform an insert a
records or rows to the table.
Understand with Example
The Tutorial illustrate an example from Mysql Trigger After Insert. To
understand this, we create a table 'Employee'. The create table is used
to construct a table 'Employee'.
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:-
Insert into add the records or rows into a 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:-
To view a table employee we use select query that
return the records from a table 'employee'.
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:-
The create table construct a 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
-> );
|
View table:-
mysql> select * from employee_log;
|
Output:-
Create Trigger:-
The Create Trigger create a trigger 'Employee_Trigger'
on table 'employee'. The 'After insert trigger' fired the trigger after
you performed a insert operation on table.
mysql> delimiter $$
mysql> CREATE TRIGGER Employee_Trigger
-> AFTER insert ON employee
-> FOR EACH ROW
-> BEGIN
-> insert into employee_log values(new.id,new.first_name,
-> new.last_name,new.start_date,new.end_date,
-> new.city,new.description,curtime());
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
|
Query to insert into employee table:-
The insert into add the records or rows into the table
'employee'.
mysql> insert into employee values (4,'Amit','Kumar','20081225','20101203','Lucknow','Programmer');
|
Table that has been modified after update 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 | 14:55:31 |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
1 row in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
