#当新增一位同学记录时,自动在分数表中插入所有课程成绩为 0 分的记 录insertinto student values(9,'xxx','女','一','','',null)createtrigger t_student_ins afterinserton student for each rowbegininsertinto score(stuid,courseid,score)select new.stuid,courseid,0from course;end;select*from score
#当删除一位同学时,把主要信息记录到备份表中createtable student_bak(
stuid intprimarykey,
name varchar(20)notnull)deletefrom student where stuid=8createtrigger t_student_del before deleteon student for each rowbegininsertinto student_bak values(old.stuid,old.name);end;#当修改一位同学的姓名时,如果有重名则不允许修改。update student set name='李钊钊'where stuid=1;insertinto student values(10,'李钊钊','女','一','','',null)createtrigger t_student_upd_name before updateon student for each rowbegindeclare c intdefault0;selectcount(*)into c from student where name=new.name;if c >0then#名字已存在,抛自定义异常事务会自动回退,触发器中不允许出现事务命令
SIGNAL SQLSTATE 'TX000'SET MESSAGE_TEXT ='name already exist';endif;end;#学生性别只能是男或女insertinto student values(11,'xxx','男','一','','',null)createtrigger t_student_sex before inserton student for each rowbeginif new.sex notin('男','女')then
SIGNAL SQLSTATE 'TX000'SET MESSAGE_TEXT ='sex only women or man';endif;end;
MySQL触发器小案例:#当新增一位同学记录时,自动在分数表中插入所有课程成绩为 0 分的记 录insert into student values(9,'xxx','女','一','','',null)create trigger t_student_ins after insert on student for each rowbegin insert into score(stuid,courseid,score) select new.stuid,courseid,0 from c