mysql create the trigger. stored procedures. functions as examples

2010-11-18  来源:本站原创  分类:Database  人气:75 

#############  This blog directly in mysql Execute in the client  #############
# 0.  Table prepared
drop database abc;
create database abc;
use abc;
create table userinfo(userid int, username varchar(10), userbirthday date);
create table userinfolog(logtime datetime, loginfo varchar(100));

# 1.  Trigger
delimiter //
create trigger beforeinsertuserinfo
before insert on userinfo
for each row begin
    insert into userinfolog values(now(), concat(new.userid, new.username));
end;
//
delimiter ;
show triggers;

# 2.  Stored procedure
drop procedure if exists spinsertuserinfo;
delimiter //
create procedure spinsertuserinfo(puserid int, pusername varchar(10), puserbirthday date)
begin
insert into userinfo values(puserid, pusername, puserbirthday);
end;
//
delimiter ;
show procedure status like 'spinsertuserinfo';
call spinsertuserinfo(1, 'zhangsan', '1972-01-09');
call spinsertuserinfo(2, 'lisi', '1982-07-26');
call spinsertuserinfo(3, 'wanger', current_date);

# 3.  Custom function
drop function if exists fngetage;
delimiter //
create function fngetage(pbirthday date)
     returns integer
begin
    return year(now()) - year(pbirthday);
end
//
delimiter ;

# 4.  View
create view viewuserinfo as select *, fngetage(userbirthday) as userage from userinfo;
select * from viewuserinfo;

This sample implements the following effects:

0.test userinfo user information database with user information table and userinfolog log table

1. Userinfo table to create a new record will be added when the trigger log into the userinfolog

(2) establish a new record to the userinfo table stored procedure

3. Userinfo table according to the date of birth field we will create a simple custom function considered age

4 Create a view called the age function userinfo

相关文章