MySQL After Trigger
This example illustrates how to create the trigger and what is the use it.
This example illustrates how to create the trigger and what is the use it.MySQL After Trigger
This example illustrates how to create the trigger and what is the use it.
In this example we create a table "Roseindia" with 'id', 'first_name', 'last_name', 'start_date', 'end_date', 'city' and 'description'. A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.
Query
CREATE TABLE Roseindia(
id int,
first_name VARCHAR(30),
last_name VARCHAR(15),
start_date DATE,
end_date DATE,
city VARCHAR(10),
description VARCHAR(15)
);
Output
+------+------------+------------+------------+------------+---------+-------------+ | id | first_name | last_name | start_date | end_date | city | description | +------+------------+------------+------------+------------+---------+-------------+ | 1 | sandeep | suman | 2008-12-25 | 2010-06-25 | lucknow | Programmer | | 2 | amit | raghuvansi | 2007-11-22 | 2010-04-21 | lucknow | Programmer | | 3 | amar | patel | 2006-10-12 | 2007-05-12 | Lucknow | Programmer | +------+------------+------------+------------+------------+---------+-------------+
Now, we create a log table "Roseindia_log" with 'id', 'first_name', 'last_name', 'start_date', 'end_date', 'city', 'description', 'Lasinserted'.
Query
CREATE TABLE Roseindia_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
);
select * from roseindia_log;
Output
Empty set (0.00 sec)
Now, create a trigger "Roseindia_Trigger". This trigger is used to insert the data in Roseindia_log table after inserting the data in Roseindia table.
Query
delimiter $$
CREATE TRIGGER Roseindia_Trigger
AFTER insert ON Roseindia
FOR EACH ROW
BEGIN
insert into Roseindia_log values(new.id,new.first_name,
new.last_name,new.start_date,new.end_date,
new.city,new.description,curtime());
END$$
Output
+-------------------+--------+-----------+----------------------------------------------------------------------------
--------------------------------------------------------------------------------------+--------+---------+------------
----------------------------------------------------+----------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode
| Definer |
+-------------------+--------+-----------+----------------------------------------------------------------------------
--------------------------------------------------------------------------------------+--------+---------+------------
----------------------------------------------------+----------------+
| Roseindia_Trigger | INSERT | roseindia | BEGIN
insert into Roseindia_log values(new.id,new.first_name,
new.last_name,new.start_date,new.end_date,
new.city,new.description,curtime());
END | AFTER | | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
+-------------------+--------+-----------+----------------------------------------------------------------------------
--------------------------------------------------------------------------------------+--------+---------+------------
----------------------------------------------------+----------------+
After creating the trigger when we insert any data in the "roseindia" table it would automatically inserted in the roseindia_log table by the trigger.
Query
insert into roseindia values(21,'AAA','DDD','20091203','20061022','VVV','KKK');
select * from roseindia_log;
Query
+------+------------+-----------+------------+------------+------+-------------+-------------+ | id | first_name | last_name | start_date | end_date | city | description | Lasinserted | +------+------------+-----------+------------+------------+------+-------------+-------------+ | 21 | AAA | DDD | 2009-12-03 | 2006-10-22 | VVV | KKK | 15:37:57 | +------+------------+-----------+------------+------------+------+-------------+-------------+
