Home Sql Create Before Insert Trigger in SQL
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Create Before Insert Trigger in SQL

Advertisement
The Create Before Insert Trigger in SQL fires the trigger before we insert the value into the table.

Create Before Insert Trigger in SQL

     

The Create Before Insert Trigger in SQL fires the trigger before we insert the value into the table.

Understand with Example

The given Tutorial describe you a code on 'Create Before Insert Trigger in SQL'. To grasp this example, we create a table 'Stu_Table'. The createtable is used to create a table 'Stu_Table' with field attribute and data type respectively.

Create Table Stu_Table

Create Table Stu_Table
(Stu_Id int,Stu_Name Varchar(15),Sub1 int,Sub2 int,Sub3 int,
Sub4 int,Sub5 int,total int,per float,status varchar(15));

Create Trigger Stu_Insert

Now, we create a Trigger 'Stu_Insert' on table stu_table. The Before Insert ON  trigger is fired first before adding a records or rows to the table 'Stu_Table'.  

delimiter $$
CREATE TRIGGER stu_insert
Before Insert ON stu_table FOR EACH ROW
BEGIN
	set new.total = new.sub1 + new.sub2 +new.sub3 +
			new.sub4 +new.sub5;
	set new.per = new.total/5;
	if new.per<33 then 
		set new.status="fail";
	elseif new.per>=33 and new.per<45 then
		set new.status="3rd Div";
	elseif new.per>=45 and new.per<60 then
		set new.status="2nd  Div";
	else
		set new.status="1st Div";
	end if;
END$$
delimiter ;

Insert Data Into Stu_Table

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
			 values (1, 'AAA', 6, 6, 6, 6, 6);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
			 values (2, 'BBB', 33, 33, 33, 33, 40);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
			 values (2, 'CCC', 45, 45, 45, 45, 50);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
			 values (2, 'DDD', 67, 67, 67, 67, 67);

Stu_Table

+------+--------+----+----+----+----+----+-----+------+----------+
|Stu_Id|Stu_Name|Sub1|Sub2|Sub3|Sub4|Sub5|total| per  | status   |
+------+--------+----+----+----+----+----+-----+------+----------+
| 1    | AAA    | 6  | 6  | 6  | 6  | 6  | 30  | 6    | fail     |
| 2    | BBB    | 33 | 33 | 33 | 33 | 40 | 172 | 34.4 | 3rd Div  |
| 2    | CCC    | 45 | 45 | 45 | 45 | 50 | 230 | 46   | 2nd  Div |
| 2    | DDD    | 67 | 67 | 67 | 67 | 67 | 335 | 67   | 1st Div  |
+------+--------+----+----+----+----+----+-----+------+----------+
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: December 31, 2008

Ask Questions?    Discuss: Create Before Insert Trigger in SQL  

Post your Comment


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