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;
---------------------------------------------------------------------------------------
How To Use MySQL Triggers | Triggers | MySQL
Reviewed by IPR
on
November 07, 2022
Rating:
No comments: