数据库表结构
student表
dorm表
1、为student表建立触发器,当向学生表中插入宿舍id时,自动将dorm表该宿舍已住人数加1
create trigger triggerNumber
before insert on student
for each row
begin
if new.dorm_id is not null
then
update dorm set alreadyNumber=alreadyNumber+1 where id=new.dorm_id;
end if;
end;
2、为student表建立触发器,当删除学生信息时,将关联的宿舍人数减1
create TRIGGER studentDelete
before delete on student
for each row
begin
if old.dorm_id is not null
then
update dorm set alreadyNumber=alreadyNumber-1 where id=old.dorm_id;
end if;
end;