Share on Google+Share on Google+

MySQL After Trigger

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

Advertisements

Advertisement

Posted on: December 9, 2010 If you enjoyed this post then why not add us on Google+? Add us to your Circles

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.