#这是一个表
create table t_courses(
`id` int(11) not null auto_increment primary key,
`name` varchar(20) not null,
`score` int(4) not NULL default 0,
`userid` varchar(32) NOT NULL
)CHARSET=utf8;

#创建一个用户表 t_persons
create table t_persons(
`id` int(11) not null auto_increment primary key,
`name` varchar(30) not null,
`age` int(3) not null default 0,
`height` int(3) not null default 0,
`weight` int(3) not null default 0
)CHARSET=UTF8;

#trigger 扳机 触发器
/*
delimiter //
create trigger g_afterinsert_persons
after insert
on t_persons for each row
begin
insert into t_courses(`name`,`source`,`userid`) values ('入学教育',0,new.id);
end;
//
*/


DELIMITER //
CREATE TRIGGER g_afterinsert_persons
AFTER INSERT
ON t_persons FOR EACH ROW
BEGIN
INSERT INTO t_courses(`name`,`score`,`userid`) VALUES ('入学教育',0,new.id);
END
//

#删除触发器 
drop trigger ;

#插入一条用户
insert into t_persons (`name`,`age`,`height`,`weight`) values ("张三",19,180,90);
INSERT INTO t_persons (`name`,`age`,`height`,`weight`) VALUES ("李四",19,180,90);

#更新事件触发器

delimiter //
create trigger g_afterupdate_persons
after update
on t_persons for each row
begin
if old.name='张三' then
update t_courses set score=0 where userid = old.id;
end if;
end
//

select * from t_courses where userid = 1;

#删除
drop trigger g_afterinsert_persons;

update t_persons set age=10 where name = "张三";


delimiter //
create trigger g_beforeinsert_persons
before insert
on t_persons for each row
begin
if length(new.name) < 3 then
set new.name="插入姓名的长度不能小于3";
end if;
end
//

#删除触发器
drop trigger g_beforeinsert_persons;