How To Use MySQL Triggers | Triggers | MySQL

 

MySQL triggers apply restrictions to tables when adding, updating, or removing table rows.

-----------------------------------------------------------------------------------------

#Before Insert

#A trigger will fire when a new student get admission and his/her marks will be 

#increased by 2 just before inclusion in the table. 

create table student (sname varchar(20), marks int);

insert into student values ("A", 70),("B", 50),("C", 80),("D", 90);

select * from student;

delimiter $$

create TRIGGER calculate

before INSERT on student

FOR EACH ROW

begin

set new.marks=new.marks+2;

end$$

insert into student values ("F", 40);

select * from student;

-----------------------------------------------------------------------------------------

#After Insert

#A trigger will fire after inclusion of a new student and marks will be inserted in a new table.

create table student1 (sname varchar(20), marks int);

insert into student1 values ("A", 70),("B", 50),("C", 80),("D", 90);

select * from student1;

create table Final_mark(total_marks int);

delimiter $$

create TRIGGER cal

after INSERT on student1

FOR EACH ROW

begin

insert into Final_mark values(new.marks);

end$$

insert into student1 values ("G", 45);

select * from student1;

select * from Final_mark;

---------------------------------------------------------------------------------

#After delete

#Create a replica table of studentnew. Now a trigger will fire when a studentnew will be 

#deleted and all the marks in replica table will be increased by 2.

create table studentnew (sname varchar(20), marks int);

insert into studentnew values ("A", 70),("B", 50),("C", 80),("D", 90);

select * from studentnew;

create table student_replica(sname varchar(20), marks int);

insert into student_replica values ("A", 70),("B", 50),("C", 80),("D", 90),("F", 42),("G", 47);

select * from student_replica;

delimiter $$

CREATE TRIGGER student_delete 

AFTER DELETE ON studentnew

FOR EACH ROW

begin

UPDATE student_replica 

SET student_replica.marks=student_replica.marks+2;

end$$

delete from studentnew where marks=70;

------------------------------------------------------------------------------------------

#Before Update

#A trigger will fire before the value of any student getting updated and 

#all the marks in replica table will be decreased by 10.

create table studentnew1 (sname varchar(20), marks int);

insert into studentnew1 values ("A", 70),("B", 50),("C", 80),("D", 90);

select * from studentnew1;

create table student_replica1(sname varchar(20), marks int);

insert into student_replica1 values ("A", 70),("B", 50),("C", 80),("D", 90),("F", 42),("G", 47);

select * from student_replica1;

delimiter $$

CREATE TRIGGER student_update1 

BEFORE UPDATE ON studentnew1

FOR EACH ROW

begin

UPDATE student_replica1 

SET student_replica1.marks=student_replica1.marks-10;

end$$

UPDATE studentnew1 set studentnew1.sname="NEW" where marks=90;

select * from student_replica1;

---------------------------------------------------------------------------------------