Introduction to MYSQL functions ,stored procedures and triggers
What is MYSQL function
In simple language ,MYSQL function is a stored program that you can pass parameters into and then return a value.BASIC SYNTAX :-
create function FUNCTION_NAME(input_parameter datatype ) returns datatype
begin
DECLARE VARIABLES DATATYPE ;
logic you want to apply ;
return variable_name / resultset ;
end ;
LETS TRY WITH EXAMPLE :-
Function to convert input string to upper case. There is already a inbuilt function for this i.e. upper(input parameter), however just for understanding purpose :-
DELIMITER //
create function fn_upper_convert(input_string varchar(255)) returns varchar(255)
begin
declare var_newstring varchar(255) ;
set var_newstring =upper(input_string) ;
return var_newstring ;
end ; //
DELIMITER ;
How to invoke function ?
To execute function below command can be used :-
select FUNCTION_NAME('input parameter') ;
E.G. select fn_upper_convert('mysqlgyan') ;What is MYSQL stored procedure
A stored procedure is a basically segment of SQL statements which can be written as per required business logic . A stored procedure can be invoked by triggers, other stored procedures, and applications such as PHP etc.BASIC SYNTAX :-
create procedure PROCEDURE_NAME()
begin
DECLARE VARIABLES DATATYPE ;
logic you want to apply ;
end ;
LETS TRY WITH EXAMPLE :-
Procedure which greets to input name given : -
DELIMITER //
create procedure sp_greet(input_name varchar(255))
begin
select concat("hello ",input_name," ! Have a nice day !") ;
end ; //
DELIMITER ;
How to invoke stored procedure ?
Stored procedure can be invoked by keyword "call" i.e.
call PROCEDURE_NAME(input parameter if any) ;
E.G. call sp_greet('MYSQLGYAN') ;Difference between MYSQL function and MYSQL stored procedure ?
-Function should always return a value , Stored procedure may or may not return a value. -Function can return only single value whereas procedure can return more than one values. -Function can be used in select/where/having SQL queries wheras procedures cannot. -Dynamic SQL is not allowed in functions whereas procedure can.What is MYSQL triggers
Trigger is set of SQL statements executed automatically when an event such as insert/update/delete associated with specific table occurs. Triggers are very useful for maintaining logs ,Data integrity etc.Types of triggers
There are two types of triggers :-
Before Trigger-Before trigger executes before insert / update / delete.
After Trigger-After trigger executes after insert / update / delete.
NOTE :- Triggers may put additional burden on your database and if table is too large it might slow down the queries as well ,So use it wisely.
BASIC SYNTAX :-
create trigger TRIGGERNAME TIME(after/before) EVENT(insert/update/delete) on TABLE_NAME
for each row
begin
logic you want to apply ;
end ;
LETS TRY WITH EXAMPLE :-
Lets create tables first
create table tbl_employee_detail(
empid varchar(100) not null ,
department varchar(100) not null ,
primary key idx_empid(empid),
key idx_department(department)
) ;
create table tbl_employee_detail_log(
empid varchar(100) not null ,
department_old varchar(100) not null ,
department varchar(100) not null ,
action_done varchar(100) not null ,
key idx_empid(empid)
) ;
Trigger to log changes after update : -
DELIMITER //
create trigger tg_log after update on tbl_employee_detail
for each row
begin
insert into tbl_employee_detail_log(empid,department_old,department,action_done)
values(new.empid,old.department,new.department,'update') ;
end ; //
DELIMITER ;
VIDEO TUTORIALS
Create Function
Create Stored Procedure
Create Trigger
Support us by sharing our content :-
Support us on Patreon :-
Lets create tables first
create table tbl_employee_detail(
empid varchar(100) not null ,
department varchar(100) not null ,
primary key idx_empid(empid),
key idx_department(department)
) ;
create table tbl_employee_detail_log(
empid varchar(100) not null ,
department_old varchar(100) not null ,
department varchar(100) not null ,
action_done varchar(100) not null ,
key idx_empid(empid)
) ;
Trigger to log changes after update : -
DELIMITER //
create trigger tg_log after update on tbl_employee_detail
for each row
begin
insert into tbl_employee_detail_log(empid,department_old,department,action_done)
values(new.empid,old.department,new.department,'update') ;
end ; //
DELIMITER ;
VIDEO TUTORIALS
Create Function
Create Stored Procedure
Create Trigger
Support us by sharing our content :-
Support us on Patreon :-
DELIMITER //
create trigger tg_log after update on tbl_employee_detail
for each row
begin
insert into tbl_employee_detail_log(empid,department_old,department,action_done)
values(new.empid,old.department,new.department,'update') ;
end ; //
DELIMITER ;