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

Create Before Insert Trigger in SQL

Create Before Insert Trigger in SQL

Create Before Insert Trigger in SQL

Tutorials