触发器 补考表

create table s1(
id int unsigned auto_increment,
name varchar(30),
score tinyint unsigned,
dept varchar(50),
primary key(id)
)
create table s2 like s1;

insert s1(name,score,dept) value(‘李四’,90,‘计算机科学’);
insert into s1 value(null,‘丽丽’,45,‘英语’);
insert s1 set name=‘张三丰’,score=25,dept=‘英语’;

delimiter $
create trigger st1 after insert on s1 for each row
begin
if new.score < 60 then
insert into s2 value(new.id,new.name,new.score,new.dept);
end if;
end $
delimiter;

select * from s1;
select * from s2;
– 修改数据
delimiter $
create trigger st2 after update on s1 for each row
begin
if new.score < 60 then
replace into s2 value(new.id,new.name,new.score,new.dept);
else
delete from s2 where id=new.id;
end if;
end $
delimiter;

update s1 set score=55 where id=2;

– 删除数据
delimiter $
create trigger st3 after delete on s1 for each row
begin
delete from s2 where id=old.id;
end $
delimiter;

delete from s1 where id=4;

– 删除触发器
drop trigger st2;
– 显示触发器
show triggers from db;