1.变量提示
NEW 是新值-- OLD 是旧值
INSERT 只有NEW ----UPDATE有NEW和OLD ---DELETE只有OLD
2.准备测试表(userinfo、userinfolog)
use test;
create table userinfo(userid int,username varchar(10),userbirthday date);
create table userinfolog(logtime datetime,loginfo varchar(100));
describe userinfo;
3.建立同时插入两张表的触发器beforeinsertuserinfo
# 定义触发器
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;
4.建立插入数据存储过程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',current_date);
select * from userinfo;
5.自定义函数fngetage
update userinfo
set userbirthday='2000.01.01'
where userid='1';
drop function if exists fngetage;
# 函数定义
delimiter $$
create function fngetage(pbirthday date)
returns integer
begin
return year(now()) - year(pbirthday);
end;
$$
delimiter ;
6.建立视图viewuserinfo调用函数fngetage
# 建立视图
create view viewuserinfo
as select * ,fngetage(userbirthday) as userage from userinfo;
select * from viewuserinfo;
清除日志记录
truncate table userinfolog;
delete from userinfolog;
本文转自 pgmia 51CTO博客，原文链接:http://blog.51cto.com/heyiyi/1251397
