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;