Mysql触发器练习

drop database if exists sunwuhao_liuyan ;
create database sunwuhao_liuyan default character set=utf8;
use sunwuhao_liuyan;

drop table if exists user;
create table user(
id int primary key AUTO_INCREMENT,
name varchar(20) ,
sex varchar(20) check (sex='男' or sex='女')
)auto_increment=1001;

insert into user values(null,'zhangsan1','男');
insert into user values(null,'zhangsan2','女');
insert into user values(null,'zhangsan3','女');
insert into user values(null,'zhangsan4','男');
insert into user values(null,'zhangsan5','男');
select * from user;

drop table if exists comment;
create table comment(
c_id int primary key auto_increment,
u_id int,
name varchar(20),
content varchar(100),
FOREIGN KEY (u_id) references user(id) on delete cascade

)auto_increment=101;

insert into comment values(null,1001,'xiao1','阿巴阿巴1001');
insert into comment values(null,1001,'xiao2','阿巴阿巴1001');
insert into comment values(null,1002,'xiao3','阿巴阿巴1');
insert into comment values(null,1003,'xiao4','阿巴阿巴1');
insert into comment values(null,1004,'xiao5','阿巴阿巴1');
insert into comment values(null,1005,'xiao6','阿巴阿巴1');
select * from comment;

--1、自行设计表中字段的数据，自行录入数据，因为该实验由验证性，逐步转为数据的半设计型

select * from user;
select * from comment;

--2、编个触发器，解决如下问题：
-- （1）更新user表的name时，触发器同时更新comment表
drop TRIGGER if exists trig1;
create TRIGGER trig1
AFTER update on user
for each row
begin
update user u join comment c on c.u_id=u.id
set c.name=u.name;
end;

update user set name='120003' where id=1001;
select * from user;
select * from comment;

-- （2）用户被删除时，comment表中有关该用户的数据将被删除。类似情景：当微信中屏蔽掉某个用户时，此用户显示在微信评论区中的有关记录信息也将被删除，包括其历史数据。

drop trigger if exists trig2;
create trigger trig2
after delete on user
for each row
delete from comment WHERE u_id =old.id;
delete from user where id=1002;

-- （3）建议在MySQL的命令行中完成为主，也可结合navicat中完成。
-- （4）代码及运行结果按步骤截图，提交。建议步骤：第一步 创建表；第二步 插入数据；第三步 更新name时触发器， 第四步 删除用户时 触发器。