Home Tutorial Php Sql MySQL After Trigger

 
 

Share on Google+Share on Google+
MySQL After Trigger
Posted on: December 9, 2010 at 12:00 AM
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    |
+------+------------+-----------+------------+------------+------+-------------+-------------+
Advertisement

Related Tags for MySQL After Trigger:


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.

Posted on: December 9, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com