This example achieves the following results:
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 a userinfo table, the 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. Trigger
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, 'hangsan', current_date );
Mysql> select * From userinfo;
3. custom function
mysql> Update userinfo
-> set userbirthday = '2017. 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;
this article is taken from the "Blue network" http://www.lan27.com/Article/200807/7557.htm