Online Training Spring Hibernate Training in Delhi R Programming Training Course Big Data and Hadoop Training Java Training

Create After Insert Trigger in SQL

Create a Trigger is a special kind of stored procedure, that fires when we perform modification on the specific data on the specified table.

Ads

Create After Insert Trigger in SQL

     

Create a Trigger is a special kind of stored procedure, that fires when we perform modification on the specific data on the specified table.

Understand with Example

The Tutorial illustrate a example from 'Create After Insert Trigger in SQL'. In this Example, we create a table 'Stu_Table' and 'Stu_Log' using create table statement.

Create Table Stu_Table

Create Table Stu_Table( Stu_Id int, Stu_Name varchar(15),Stu_Class int);

Create Table Stu_Log

create table stu_log( user_id VARCHAR(15), description VARCHAR(100));

Here is the video tutorial of: "How to create insert after trigger in MySQL?"

Create Trigger Stu_Insert

The Query create a  trigger stu_insert on table 'stu_table'. Whenever we perform insert values into a table 'stu_table', an insert trigger is fired and performed for each rows in the table. Once the trigger operation is performed ,this will run the query inside the begin-end statement. The insert into add the records or rows value to the table stu_log.

delimiter $$
      CREATE TRIGGER stu_insert
      AFTER insert ON stu_table FOR EACH ROW
BEGIN

	INSERT into stu_log(user_id, description)
        VALUES (user(), CONCAT('Insert Student record ',
	new.stu_id,' ',new.stu_name,' ',new.stu_class));

END$$
delimiter ;

Insert Data Into Stu_Table

insert into stu_table values(1, 'Komal',10);
insert into stu_table values(2, 'Ajay',10);
insert into stu_table values(3, 'Santosh',10);
insert into stu_table values(4, 'Rakesh',10);
insert into stu_table values(5, 'Bhau',10);

Stu_Table

+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1      | Komal    | 10        |
| 2      | Ajay     | 10        |
| 3      | Santosh  | 10        |
| 4      | Rakesh   | 10        |
| 5      | Bhau     | 10        |
+--------+----------+-----------+

Stu_Log

+----------------+------------------------------------+
| user_id        | description                        |
+----------------+------------------------------------+
| [email protected] | Insert Student record 1 Komal 10   |
| [email protected] | Insert Student record 2 Ajay 10    |
| [email protected] | Insert Student record 3 Santosh 10 |
| [email protected] | Insert Student record 4 Rakesh 10  |
| [email protected] | Insert Student record 5 Bhau 10    |
+----------------+------------------------------------+

Advertisements

Share on Google+Share on Google+

Create After Insert Trigger in SQL

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

Advertisements

 

Discuss: Create After Insert Trigger in SQL   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:2
prakash
October 30, 2011
new.

here two place new is used new.stu_name,' ',new.stu_class i want to know why this new is used
Sanch
February 23, 2012
Giving syntax error

It is giving syntax error. Is it applicable for SQL server?

Ads

 

Ads