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

 
 

Share on Google+Share on Google+

Create Before Update Trigger in SQL

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

If you enjoyed this post then why not add us on Google+? Add us to your Circles



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

Related Tutorials

Ask Questions?    Discuss: Create Before Update Trigger in SQL   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
phanikumar
October 11, 2011
Querry

Hi This is phanikumar i am same above example copying but some errors are occured in executing triggers that are ................ Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '$'. Msg 111, Level 15, State 1, Line 5 'CREATE TRIGGER' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '.'. Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'then'. ............................... please help me as early as possible
bhanendra pratap
November 16, 2011
java

program
DMCA.com