Trigger in MySQL

In this post we will see how to use Trigger in MySQL.

What is a Trigger:
A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event that is associated with a table occurs e.g., insert, update or delete.

How to create trigger
Example one :

step-1

create database cad_test;

step-2

use cad_test;

step-3

create table attendance(idattendance int not null , primary key ('idattendance'));

step-4

create table attendance_1(idattendance_1 int not null , primary key ('idattendance_1'));

step-5 (this step will create a trigger)

create trigger trigger_attendance
after insert on attendance
for each row
insert into attendance_1 values (new.idattendance);

step-6

select * from attendance;
select * from attendance_1; 

step-7 (now when you insert values in attendance then those values also copy in attendance_1)

insert into attendance values(12345);

step-8

select * from attendance;
select * from attendance_1;

now you see you are updating the value only in table attendance but it also automatically updated in table attendance_1.

Note:- above example is useful when only one command is use in trigger. Like in above example we use “insert into attendance_1 values (new.idattendance);”
so if we want to use more then one command in trigger then we have to use delimiter. Example of delimiter is  given below.

 
Example two :

step-1

create database abhi;

step-2

use abhishek;

step-3

CREATE  TABLE `table_one` (
  `idtable_one` INT NOT NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
  `college` VARCHAR(45) NULL ,
  PRIMARY KEY (`idtable_one`) );

step-4

CREATE  TABLE `table_two` (
  `idtable_two` INT NOT NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
  `college` VARCHAR(45) NULL ,
  PRIMARY KEY (`idtable_two`) );

step-4 ( create delimiter for multiple statement)

delimiter *
create trigger trigger_abhishek
after insert on table_one
for each row begin
insert into table_two values (new.idtable_one , new.first_name , new.last_name , new.college);
end;
*
delimiter ;
show triggers;

Note: you can add multiple statement in
begin
…..
…..
end
and for this you have to use delimiter.