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

Create After Insert Trigger in SQL

This page discusses - Create After Insert Trigger in SQL

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));

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

Ads
Share on Google+Share on Google+

Create After Insert Trigger in SQL

Posted on: April 18, 2011 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: Create After Insert Trigger in SQL  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
saranya jothiprakasam
November 23, 2011
my sqlerror 1442

i got the error as can't update table dctblloaddata in stored function/trigger because it is already used by statement which invoked this stored fuction/trigger when trying to insert the row.. my trigger is as follows delimiter $$ create trigger s after insert on dctblloaddata for each row begin declare v_value long; select LoadProfileID from cfw.dctblloaddata order by LoadProfileID desc limit 1 into v_value; update `dctblloaddata` set AvgCurrent=-99999.99, AvgVolt=-99999.99, ActiveDemandFwd=-99999.99, ApperentDemandFwd=-99999.99 where ((LoadProfileID=v_value ) AND (AvgCurrent>800 OR AvgCurrent = 0 OR AvgCurrent =-99999.99 OR AvgVolt >300 OR AvgVolt=0 OR AvgVolt=-99999.99 OR ActiveDemandFwd=0 OR ActiveDemandFwd=-99999.99 OR ApperentDemandFwd=0 OR ApperentDemandFwd=-99999.99)) ; end $$ delimiter ; give me some suggestion for me why it is not properly working.

Ads

 

Ads