Views and Triggers in MYSQL
In this blog, we are going to talk about Views and Triggers in MySQL.
Views :
virtual or temporary table which stores the result set of another query/another SQL statement. It contains rows and columns just like real tables. View does not contain its own data instead it contains data from one or more tables. We can create a view with select statements only. One view can contain columns from one or more tables. If we make any changes to the original table then those changes are reflected in view also.
Creating View
Consider the following table stud:
select * from stud;
+—-+—————+—————+——-+—————+——+
| id | name | course | marks | mobileno | age |
+—-+—————+—————+——-+—————+——+
| 1 | sham | NODE | 105 | 8797678990 | 20 |
| 2 | gita | SS | 93.1 | 87976789 | 20 |
| 3 | Not specified | Not specified | 110 | 0 | 30 |
| 4 | riya | testing | 78.8 | 87976789 | 25 |
| 5 | Not specified | web | 72.9 | 9997976789 | 23 |
| 6 | Not specified | ss | 10 | 0 | 25 |
| 7 | jay | Not specified | 78 | Not specified | 0 |
+—-+—————+—————+——-+—————+——+
#creating view on a Single table
create view course details as select * from stud where course=”web” or course=”testing”;
#calling view
select * from courseDetails;
+—-+—————+———+——-+————+——+
| id | name | course | marks | mobileno | age |
+—-+—————+———+——-+————+——+
| 4 | riya | testing | 78.8 | 87976789 | 25 |
| 5 | Not specified | web | 72.9 | 9997976789 | 23 |
+—-+—————+———+——-+————+——+
#updating view using alter
alter view courseDetails as select * from stud where
(course=”web” or course=”testing”) and marks > 70;
select * from courseDetails;
#renaming view
rename table coursedetails to coursedetails1;
select * from courseDetails1;
#dropping view
drop view coursedetails;
#creating view onMultiple tables
Consider below table book:
select * from book;
+——+——-+——-+
| id | bname | price |
+——+——-+——-+
| 45 | math | 780.9 |
| 46 | eng | 980.9 |
| 44 | hindi | 800.9 |
+——+——-+——-+
When we create a view based on more than one table then each select statement in the create view query contains the same no
create view multipleData as select id, name, marks from stud where marks > 70
UNION
select id,name,price from book;
#calling view
select * from multipleData1;
+——+—————+——-+
| id | name | marks |
+——+—————+——-+
| 1 | sham | 105 |
| 2 | gita | 93.1 |
| 3 | Not specified | 110 |
| 4 | riya | 78.8 |
| 5 | Not specified | 72.9 |
| 7 | jay | 78 |
| 45 | math | 780.9 |
| 46 | eng | 980.9 |
| 44 | hindi | 800.9 |
+——+—————+——-+
In the above table, the first 5 records belong to “stud” table, and rest 4 records belong to “book” table
Triggers:
Triggers in MySQL are a set of SQL statements that will get executed before or after insert, update, and delete operations. In other words, we can say that triggers are the events that will get executed or fire when we perform either of the 3 operations like insert, update, or delete.
Syntax for creating Trigger:
delimiter delimiter_name
create trigger triggerName
(before|after) (insert | update| delete ) ON tableName
for each row
begin
#trigger code
#sql statements
end $$
1. Before insert
create table person1(id int primary key,name varchar(55),age int);
#before insert => the below trigger gets fire/execute when we are trying to insert negative age into Person1 table….and if the value of age is negative then it set 0 in the age column
delimiter $$
create trigger beforeInsertingData
before insert ON person1
for each row
begin
if new.age < 0 then set new.age=0;
end if;
end $$
—————————————————————
the trigger will not get fire for the below insert statement because we are inserting a positive value in the age
insert into person1 values(103,”jay”,28);
+—–+——–+——+
| id | name | age |
+—–+——–+——+
| 103 | jay | 28 |
the trigger will fire for the below insert statement because we are inserting negative value in the age column which will get set to zero..
insert into person1 values(104,”ajay”,-13);
+—–+——–+——+
| id | name | age |
+—–+——–+——+
| 103 | jay | 28 |
| 104 | ajay | 0 |
+—–+——–+——+
2. After insert
create table person2(id int primary key auto_increment,name varchar(77),dob date);
create table msg(id int primary key auto_increment, pid int,msg varchar(400));
The below trigger will get executed when we insert the value of the DOB column in the person2 table as null. After we insert a null value this trigger inserts a new statement in “msg” table which stores person id and message like “hello “,new.name,” your dob is null..please insert valid DOB” in msg table
delimiter %%
create trigger afterInsertingData
after insert ON person2
for each row
begin
if new.dob is null then
insert into msg(pid,msg) values(new.id,concat( “hello “,new.name,” your dob is null..please insert valid DOB”));
end if;
end %%
#For the below insert statement above trigger is not executed because we not setting DOB as null
insert into person2 values(1,”ram”,”2000-03-12″);
select * from person2;
+—-+——-+————+
| id | name | dob |
+—-+——-+————+
| 1 | ram | 2000-03-12 |
select * from msg;
Empty set…
#For the below insert statement above trigger is executed because we setting DOB as null
insert into person2(id,name) values(2,”sham”);
select * from person2;
+—-+——-+————+
| id | name | dob |
+—-+——-+————+
| 1 | ram | 2000-03-12 |
| 2 | sham | NULL |
select * from msg;
+—-+——+——————————————————-+
| id | pid | msg |
+—-+——+——————————————————-+
| 1 | 2 | hello sham your dob is null..please insert valid DOB |
For Free, Demo classes Call: 020 7117 1500
Registration Link: SQL Training in Pune!
2. Before Delete
create table result(sid int primary key,createdDate date, marks int);
create table resultDelete2(id int primary key auto_increment,sid int,craetedAt date,
deletedAt timestamp default now());
#the below trigger will fire/execute when we are trying to delete a record from result table
#and then will insert a new record in “resultDelete2” with created and deleted dates
delimiter %%
create trigger deletingData
before delete on result
for each row
begin
insert resultDelete2(sid,craetedAt) values(old.sid,old.createdDate);
end %%
insert into result values(1, “2024-07-01”,78);
insert into result values(2, “2024-07-04”,98);
insert into result values(3, “2024-06-30”,58);
select * from result;
select * from resultDelete2;
select * from result;
+—–+————-+——-+
| sid | createdDate | marks |
+—–+————-+——-+
| 1 | 2024-07-01 | 78 |
| 2 | 2024-07-04 | 98 |
| 3 | 2024-06-30 | 58 |
+—–+————-+——-+
delete from result where sid=3; #now before delete trigger will get fire and the new record will get inserted into “resultDelete2” table
select * from resultDelete2;
+—-+——+————+———————+
| id | sid | craetedAt | deletedAt |
+—-+——+————+———————+
| 1 | 3 | 2024-06-30 | 2024-07-16 11:02:57 |
+—-+——+————+———————+
2. Before Update
create table stud1(sid int primary key,name varchar(55), marks int ,grade varchar(200));
insert into stud1(sid,name) values(1,”alina”);
insert into stud1(sid,name) values(2,”harry”);
insert into stud1(sid,name) values(3,”sam”);
insert into stud1(sid,name) values(4,”maria”);
select * from stud1;
+—–+——-+——-+—————+
| sid | name | marks | grade |
+—–+——-+——-+—————+
| 1 | alina | NULL | NULL |
| 2 | harry | NULL| NULL |
| 3 | sam | NULL | NULL |
| 4 | maria | NULL | NULL |
+—–+——-+——-+—————+
#the following trigger will get executed when we are trying to update marks in the stud1 table
Which will update the “grade” column in the stud1 table according to the value of the new mark
delimiter **
create trigger updatingData
before update on stud1
for each row
begin
if new.marks< 50 then set new.grade=”fail”;
elseif new.marks>=50 AND new.marks < 70 then set new.grade=”distinction”;
elseif new.marks>=70 AND new.marks <=100 then set new.grade=”merit”;
else set new.grade=”invalid marks”;
end if;
end **
update stud1 set marks=66 where sid=1;
select * from stud1;
select * from stud1;
+—–+——-+——-+—————+
| sid | name | marks | grade |
+—–+——-+——-+—————+
| 1 | alina | 66 | distinction |
| 2 | harry | NULL| NULL |
| 3 | sam | NULL | NULL |
| 4 | maria | NULL | NULL |
+—–+——-+——-+—————+
update stud1 set marks=10 where sid=2;
update stud1 set marks=99 where sid=3;
update stud1 set marks=200 where sid=4;
select * from stud1;
+—–+——-+——-+—————+
| sid | name | marks | grade |
+—–+——-+——-+—————+
| 1 | alina | 66 | distinction |
| 2 | harry | 10 | fail |
| 3 | sam | 99 | merit |
| 4 | maria | 200 | invalid marks |
+—–+——-+——-+—————+
#checking existing triggers
show triggers;
#dropping existing trigger
drop trigger checkingEligibility;
Do visit our channel to learn more: Click Here
Author:-
Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!
© Copyright 2020 | SevenMentor Pvt Ltd.