场景:在考勤活动表attendance_inf中插入一条数据(即发布一个某课程的签到活动时),在考勤签到表attendance_sheet_inf中插入该课程的所有学生的初始签到信息(status=0);
触发器实现如下:
delimiter $$
create trigger `trigger_autoinsert_ash`
after insert on `attendance_inf` for each row
begin
declare done int default 0;
declare u_id varchar(20); -- 此不要与下面出现的表中字段`user_id`相同,否则循环体中的insert获取到null
-- 声明游标
declare cur_Insert cursor for select `user_id` from `user_inf` where `subject_id` = NEW.`subject_id`;
declare continue handler for not found set done = 1; -- 放在游标声明之后,默认0,当为1时跳出循环
-- 打开游标
open cur_Insert;
loop_1:loop
-- 获取结果
fetch next from cur_Insert into u_id;
-- fetch cur_Insert into u_id;
if done = 1 -- 跳出循环条件
then
leave loop_1;
end if;
insert into `attendance_sheet_inf`(`attendance_id`,`user_id`,