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;

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

How To Use MySQL Triggers | Triggers | MySQL How To Use MySQL Triggers | Triggers | MySQL Reviewed by IPR on November 07, 2022 Rating: 5

No comments:

Powered by Blogger.