MySQL之触发器实战

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#当新增一位同学记录时，自动在分数表中插入所有课程成绩为 0 分的记 录
insert into student values(9,'xxx','女','一','','',null)

create trigger t_student_ins after insert on student for each row
begin
    insert into score(stuid,courseid,score) select new.stuid,courseid,0 from course;
end;

select * from score

#当删除一位同学时，把主要信息记录到备份表中
create table student_bak(
   stuid int primary key,
   name varchar(20) not null
)

delete from student where stuid=8

create trigger t_student_del before delete on student for each row
begin
   insert into student_bak values(old.stuid,old.name);
end;

#当修改一位同学的姓名时，如果有重名则不允许修改。
update student set name='李钊钊' where stuid=1;
insert into student values(10,'李钊钊','女','一','','',null)
create trigger t_student_upd_name before update on student for each row
begin
     declare c int default 0;
     select count(*)  into c from student where name=new.name;
     if c >0 then   #名字已存在，抛自定义异常事务会自动回退，触发器中不允许出现事务命令
           SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = 'name already exist';
     end if;
end;

#学生性别只能是男或女
insert into student values(11,'xxx','男','一','','',null)
create trigger t_student_sex before insert on student for each row
begin
    if new.sex not in ('男','女') then
           SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = 'sex only women or man';
    end if;
end;