example of before insert trigger in mysql

Ads
 

example of before insert trigger in mysql

Hi,

How to create a simple before insert trigger in MySQL?

Thanks

View Answers

April 25, 2016 at 2:04 PM

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









Related Tutorials/Questions & Answers:
example of before insert trigger in mysql
example of before insert trigger in mysql  Hi, How to create a simple before insert trigger in MySQL? Thanks   Hi, The before insert... the trigger: delimiter $$ CREATE TRIGGER stu_insert Before Insert ON student
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
Advertisements
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
Mysql Trigger After Insert
The Tutorial illustrate an example from Mysql Trigger After Insert. To understand... Mysql Trigger After Insert       Mysql Trigger After Insert fired the trigger after you
Mysql Time Trigger
Mysql Time Trigger       Mysql Time Trigger is fired when a specified event is occurred. The trigger event can be before trigger, after trigger  and instead of trigger
Mysql Time Trigger
Mysql Time Trigger       Mysql Time Trigger is fired when a specified event is occurred. The trigger event can be before trigger, after trigger  and instead of trigger
Trigger
Trigger After Insert The Tutorial illustrate an example from Mysql... an example from 'Trigger After Delete' in Mysql. To understand... Trigger after Update The Tutorial understand an example from 'Mysql
Create Before Update Trigger in SQL
Create Before Update Trigger in SQL       Create Before Update Trigger in SQL is used in SQL.... Understand with Example The Tutorial illustrate an example from 'Create Before
MySQL After Trigger
MySQL After Trigger This example illustrates how to create the trigger...' and 'description'. A trigger is defined to activate when an INSERT, DELETE, or UPDATE... automatically inserted in the roseindia_log table by the trigger. Query insert
trigger after insert for deletion
trigger after insert for deletion   String query2 = "create TRIGGER trig after insert on "cart" + " FOR EACH ROW... = st1.executeUpdate( query2); i have to do the above trigger operation, but i'm stuck
throw exception in mysql trigger
throw exception in mysql trigger  throw exception in mysql trigger
Create Before Update Trigger in SQL
'Create Before Update Trigger in SQL' . To understand this example we create... float,status varchar(15)); Create Trigger Stu_Insert The Before... Create Before Update Trigger in SQL   
Create After Insert Trigger in SQL
from 'Create After Insert Trigger in SQL'. In this Example, we create... Create After Insert Trigger in SQL   ...)); Create Trigger Stu_Insert The Query create a  trigger stu_insert
Mysql Insert
Mysql Insert       Mysql Insert is used to insert the records or rows to the table. Understand with Example The Tutorial illustrate an example from 'Mysql Insert'.To grasp
trigger
trigger  write a trigger program before insert into salary that it must not below Rs 1000
trigger
trigger  write a trigger program before insert into salary that it must not below Rs 1000
Mysql Trigger after Update
an example from 'Mysql Trigger after Update'. To grasp 'Mysql Trigger after Update...Mysql Trigger after Update       Mysql Trigger after  Update fired automatically after we perform
Create Delete Trigger in SQL
;    Create Delete Trigger in SQL fire this trigger before... The Tutorial illustrate an example from 'Create Delete Trigger in SQL... trigger on table stu_delete. The Delete trigger is fired before we perform
Create After Insert Trigger in SQL
a example from 'Create After Insert Trigger in SQL'. In this Example, we... in MySQL?" Create Trigger Stu_Insert The Query create a ...Create After Insert Trigger in SQL   
insert query in mysql
insert query in mysql  insert query in mysql not working   [INSERT INTO Birthdays(firstname, lastname, birthday, group) VALUES('Sam','Smith','June','Junior
can i use trigger to insert data within a time period ?
can i use trigger to insert data within a time period ?  Hello sir, i... this trigger concept the data will be insert to each Employee's ID. Plz sir help me regarding this problem.how to solve this issue and how i will use this trigger
How to insert image in MySQL database using sql query?
Insert image in MySQL database using sql query  ... in MySQL database and then used the query to insert the data... to insert image in MySQL database using sql query?": So, lets start
Mysql Multiple Date Insert
illustrate an example from 'Mysql Multiple Column Insert'.To understand... Mysql Multiple Date Insert       Mysql Multiple Column Insert is used to add or insert
Mysql Multiple Date Insert
illustrate an example from 'Mysql Multiple Column Insert'. To understand an example... Mysql Multiple Date Insert       Mysql Multiple Column Insert is used to add or insert
MySQL PHP Insert
. Understand with Example The Tutorial illustrate an example on MySQL PHP Insert... MySQL PHP Insert       Mysql PHP Insert is used to execute the Mysql function ( ) insert
insert excel sheet into mysql as a table
insert excel sheet into mysql as a table  sir, i want to import an excel sheet into mysql5.0 database as in the table format using tomcat 6.0 by jsp
Create After Update Trigger in SQL
Create After Update Trigger in SQL       After Trigger in SQL is fired before update... illustrate an example from 'Create After Update Trigger in SQL'. In order
How to insert data into MySQL Table?
How to insert data into MySQL Table?  Hi, How to insert the data into MySQL Database? Please explain me with the examples and videos thanks. Thanks   Hi, The insert into query is used to insert the data into MySQL
how to insert excel file into mysql datbase in servlet
how to insert excel file into mysql datbase in servlet  emphashow to insert excel file into mysql datbase inservletized text
How to change image size before adding to mysql using jsp
How to change image size before adding to mysql using jsp  I want to upload an image to MySQL DB, before inserting the image I want to change... for example, i want to set image size 300x300. Plz help me out
SQL Bulk Insert Example
SQL Bulk Insert Example       SQL Bulk Insert Example is used to insert a bulk record... to create SQL Bulk Insert .To grasp this example we create a table 'Stu

Ads