MySQL After Trigger

This example illustrates how to create the "after trigger" and what is the use of it.

A trigger is a named object for the database. It is associated with a table and is activated when event like an INSERT, DELETE, or UPDATE occurs for the table. Trigger can be activated either before or after the event. The "CREATE TRIGGER" statement is used to create the trigger and  "AFTER" keyword is used to specify the trigger will be activated after the event. In this example we create a table "Roseindia" with 'id', 'first_name', 'last_name', 'start_date', 'end_date', 'city' and 'description' columns. Now we want whenever the data is inserted into this table, all the data of the inserted row and time of insertion should be copied into some other table also. For this we have created trigger named "Roseindia_Trigger" which copies the data in "Roseindia_log" table.

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

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

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$$

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');

Query

select * from roseindia_log;

Output:

+------+------------+-----------+------------+------------
| id   | first_name | last_name | start_date | end_date   
+------+------------+-----------+------------+------------
| 21   | AAA        | DDD       | 2009-12-03 | 2006-10-22 
+------+------------+-----------+------------+------------