Home Sql Create Before Update Trigger in SQL

Create Before Update Trigger in SQL
Posted on: December 31, 2008 at 12:00 AM

Create Before Update Trigger in SQL is used in SQL, if a trigger monitoring for changes to a column of the table.

Create Before Update Trigger in SQL

Create Before Update Trigger in SQL is used in SQL, if a trigger monitoring for changes to a column of the table.

Understand with Example

The Tutorial illustrate an example from 'Create Before Update Trigger in SQL' . To understand this example we create a table 'Stu_Table' with field name 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

The Before Trigger fires on a table 'stu_table' before you add a records or rows into 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 ;

Create Trigger Stu_Update

The Create Before Update Trigger in SQL monitor for changes to a column in the table 'stu_table' where stu_id is '1'.

delimiter $$

CREATE TRIGGER stu_update

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

Update Data From Stu_Table;

update stu_table set sub1=33,set sub2=33,set
sub3=33,set sub4=33,set sub5=33
where stu_id = 1;

Stu_Table

+-------+---------+----+----+----+----+----+-----+------+----------+
|Stu_Id |Stu_Name |Sub1|Sub2|Sub3|Sub4|Sub5|total| per  | status   |
+------ +---------+----+----+----+----+----+-----+------+----------+
| 1     | AAA     | 50 | 50 | 33 | 6  | 6  | 145 | 29   | 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  |
+-------+---------+----+----+----+----+----+-----+------+----------+

Related Tags for Create Before Update Trigger in SQL:
sql, c, table, date, update, monitoring, sed, column, change, trigger, monitor, create, tab, if, for, to, e, it, hang, use, changes, in, pda, m, tr, es, monit, tor, umn, trig, s, col, before, at, is, ha, ea, upd, s, s, ri, ring, th, ab, abl, lum, on, ol, o

«Previous Index Next»