Hi,
How to create a simple before insert trigger in MySQL?
Thanks
Hi,
The before insert trigger is executed before saving the data into database. Trigger is stored in MySQL database server and executed on the server itself. So, its very fast.
First create table:
create table student( id int, student_name varchar(15), subject1 int, subject2 int, subject3 int, subject4 int, subject5 int, total int, per float, status varchar(15) );
Now create the trigger:
delimiter $$ CREATE TRIGGER stu_insert Before Insert ON student FOR EACH ROW BEGIN set new.total = new.subject1 + new.subject2 +new.subject3 + new.subject4 +new.subject5; 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 ;
Now you can insert the data and check the data inserted into database. Here is the sql to insert sample data:
insert into student(id, student_name, subject1, subject2, subject3, subject4, subject5) values (1, 'AAA', 6, 6, 6, 6, 6); insert into student(id, student_name, subject1, subject2, subject3, subject4, subject5) values (2, 'BBB', 33, 33, 33, 33, 40); insert into student(id, student_name, subject1, subject2, subject3, subject4, subject5) values (2, 'CCC', 45, 45, 45, 45, 50); insert into student(id, student_name, subject1, subject2, subject3, subject4, subject5) values (2, 'DDD', 67, 67, 67, 67, 67);
You can use the following query to query the data from database:
select * from student;
Thanks
Ads