Home Sql Trigger Mysql Trigger After Insert
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Trigger After Insert

Advertisement
Mysql Trigger After Insert fired the trigger after you perform an insert a records or rows to the table.

Mysql Trigger After Insert

     

Mysql Trigger After Insert fired the trigger after you perform an insert a records or rows to the table.

Understand with Example

The Tutorial illustrate an example from Mysql Trigger After Insert. To understand this, we create a table 'Employee'. The create table is used to construct a table 'Employee'.

Create table:-

mysql> CREATE TABLE Employee(
    ->          id            int,
    ->          first_name    VARCHAR(30),
    ->          last_name     VARCHAR(15),
    ->          start_date    DATE,
    ->          end_date      DATE,
    ->          city          VARCHAR(10),
    ->          description   VARCHAR(15)
    ->      );
Query OK, 0 rows affected (0.05 sec)

Insert data into table:-

Insert into add the records or rows into a table 'Employee'.

mysql> insert into Employee values 
(01,'Girish','Tewari','20081225',  '20100625','Nainital','Programmer');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Employee values 
(02,'Komal','Choudhry','20071122', '20100421','Meerut','Programmer');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Employee values 
(03,'Mahendra','Singh','20061012',  '20070512','Lucknow','Programmer');
Query OK, 1 row affected (0.02 sec)


View table:-

To view a table employee we use select query that return the records from a table 'employee'.

mysql> select * from employee;

Output:-

+------+------------+-----------+------------+------------+----------+-------------+
| id   | first_name | last_name | start_date | end_date   | city     | description |
+------+------------+-----------+------------+------------+----------+-------------+
|    1 | Girish     | Tewari    | 2008-12-25 | 2010-06-25 | Nainital | Programmer  |
|    2 | Komal      | Choudhry  | 2007-11-22 | 2010-04-21 | Meerut   | Programmer  |
|    3 | Mahendra   | Singh     | 2006-10-12 | 2007-05-12 | Lucknow  | Programmer  |
+------+------------+-----------+------------+------------+----------+-------------+
3 rows in set (0.00 sec)

Create table:-

The create table construct a table Employee_log.

mysql> CREATE TABLE Employee_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
    ->      );

View table:-

mysql> select * from employee_log;

Output:-

Empty set (0.02 sec)

Create Trigger:-

The Create Trigger create a trigger 'Employee_Trigger' on table 'employee'. The 'After insert trigger'  fired the trigger after you performed a insert operation on table.

mysql> delimiter $$
mysql> CREATE TRIGGER Employee_Trigger
    -> AFTER insert ON employee
    -> FOR EACH ROW
    -> BEGIN
    -> insert into employee_log values(new.id,new.first_name,
    -> new.last_name,new.start_date,new.end_date,
    -> new.city,new.description,curtime());
    ->  END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

Query to insert into employee table:-

The insert into add the records or rows into the table 'employee'.

mysql> insert into employee values (4,'Amit','Kumar','20081225','20101203','Lucknow','Programmer');

Table that has been modified after update query executes is Employee_log:-

Query to view Employee_log table:-

mysql> select * from employee_log;

Output:-

+------+------------+-----------+------------+------------+---------+-------------+-------------+
| id   | first_name | last_name | start_date | end_date   | city    | description | Lasinserted |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
|    4 | Amit       | Kumar     | 2008-12-25 | 2010-12-03 | Lucknow | Programmer  | 14:55:31    |
+------+------------+-----------+------------+------------+---------+-------------+-------------+
1 row in set (0.00 sec)
Advertisements

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: June 16, 2009

Ask Questions?    Discuss: Mysql Trigger After Insert   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
malati
February 1, 2012
mysql triggers

I have 3 tables 1.send_sms 2.sent_sms amd 3.dlvr when i send msg it' be in send_sms table and the status in dlvr table will be send and when the msg is deliverd the msg is moved to sent_sms and the status of dlvr table is sent.. can you pls suggest me how to write the trigger to update dlvr status to sent from send
adi
February 19, 2012
thanks

thanks bro...
PanchoJ
February 20, 2012
export results to XML file

HI, How would you export these results to an XML file? Thanks
Sanchit Mehra
April 2, 2012
triggers in mysql

I want to ask a query. I have a database. I want to apply a trigger in that. Table are: customer,order_info customer(columns):cust_id,cust_name,total_bill order_info(columns):cust_id,order_id,bill Whenever there is an addition in the bill in order_info table, this bill should be added to the total_bill. I've written a query. Please help me out because i'm facing problem while using it with mysql QUERY: CREATE TRIGGER `update_total_bill` AFTER INSERT ON `ORDER_INFO` INNER JOIN `CUSTOMER` FOR EACH ROW BEGIN UPDATE `customer` SET `CUSTOMER.total_bill` = `CUSTOMER.total_bill` + (SELECT :NEW.bill FROM `ORDER_INFO`); WHERE `CUSTOMER.customer_id` = `ORDER_INFO.customer_id`; END
hardik
July 3, 2012
Error

when i run this delimiter $$ CREATE TRIGGER Employee_Trigger AFTER insert ON employee FOR EACH ROW BEGIN insert into employee_log values(new.id,new.first_name,new.last_name,new.start_date,new.end_date,new.city,new.description,curtime()); END$$ i got this error MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 what should i do now ?
Arindam sarkar
July 26, 2012
facing problem,when i want to create trigger

Please help me, when i tried to create that trigger,it shows error. i did, delimiter $$ CREATE TRIGGER Employee_Trigger AFTER insert ON employee FOR EACH ROW BEGIN insert into employee_log values(new.id,new.first_name,new.last_name,new.start_date,new.end_date,new.city,new.description,curtime()); END$$ delimiter ; #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' Please get me out of it.
the game
September 4, 2012
drfsgr

time to play the game
DMCA.com