MySQL trigger and stored procedure instance demonstration

The following articles mainly introduce MySQL triggers, stored procedures, and examples of functions and views. The following describes the triggers, stored procedures, and specific operation solutions of functions and views, I hope this will help you in your future studies.

Demo of MySQL triggers, stored procedures, and functions and views:

0. The test database has the userinfo user information table and the userinfolog user information log table.

1. When a new record is created for the userinfo table, the MySQL trigger adds the new log to userinfolog.

2. Create a stored procedure for adding records to the userinfo table

3. Based on the date of birth field in the userinfo table, we will create a user-defined function for simple age calculation.

4. Create a userinfo view to call the age function.

0. Prepare related tables

 
 

  
  
MySQL> use test;  
  
  
MySQL> create table userinfo(userid int,username varchar(10),userbirthday date);  
  
  
MySQL> create table userinfolog(logtime datetime,loginfo varchar(100));  
  
  
MySQL> describe userinfo; 
 
 

1. MySQL triggers

 
 

  
  
MySQL> delimiter |  
  
  
MySQL> create trigger beforeinsertuserinfo  
  
  
-> before insert on userinfo  
  
  
-> for each row begin  
  
  
-> insert into userinfolog values(now(),CONCAT(new.userid,new.username));  
  
  
-> end;  
  
  
-> |  
  
  
MySQL> delimiter ;  
  
  
MySQL> show triggers; 
 
 

2. Stored Procedure

 
 

  
  
MySQL> delimiter //  
  
  
MySQL> create procedure spinsertuserinfo(  
  
  
-> puserid int,pusername varchar(10)  
  
  
-> ,puserbirthday date  
  
  
-> )  
  
  
-> begin  
  
  
-> insert into userinfo values(puserid,pusername,puserbirthday);  
  
  
-> end;  
  
  
-> //  
  
  
MySQL> show procedure status like 'spinsertuserinfo';  
  
  
MySQL> call spinsertuserinfo(1,'zhangsan',current_date);  
  
  
MySQL> select * from userinfo; 
 
 

3. User-Defined Functions

 
 

  
  
MySQL> update userinfo  
  
  
-> set userbirthday='2000.01.01' 
  
  
-> where userid='1';  
  
  
MySQL> drop function if exists fngetage;  
  
  
MySQL> delimiter //  
  
  
MySQL> create function fngetage(pbirthday date)  
  
  
-> returns integer  
  
  
-> begin  
  
  
-> return year(now()) - year(pbirthday);  
  
  
-> end  
  
  
-> // 
 
 

4. View

 
 

  
  
MySQL> create view viewuserinfo  
  
  
-> as select * ,fngetage(userbirthday) as userage from userinfo;  
  
  
MySQL> select * from viewuserinfo; 
 
 

Clear log records

 
 

  
  
MySQL> truncate table userinfolog;  
  
  
MySQL> delete from userinfolog; 
 
 

The above content is an introduction to the MySQL trigger stored procedure function view. I hope you will find some gains.