Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Trigger aftertrigger

Related Tutorials


 
 

Share on Google+Share on Google+

aftertrigger

Advertisement
aftertrigger

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

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  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.

Posted on: April 18, 2011

Related Tutorials

null  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0
DMCA.com