Create Before Insert Trigger in SQL

This page discusses - Create Before Insert Trigger in SQL

Create Before Insert Trigger in SQL

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