首先我有两个表tb_bm和tb_yg(分别为部门表和员工表)其中员工表中的bmbh字段(即部门编号)是外键,参照部门表中的bmbh字段 现在要求建立一个触发器:当某用户删除部门表中的某一部门,那么触发器就将员工表中所有关于被删除的这个部门的记录都转lizhixinxibiao表(离职信息表)
大家请看,下面就是以上我所说的前两个表的信息和最后一个表的结构(最后这个表中目前没有任何数据):
部门表 员工表 离职信息表 最后就是我的解决方法,麻烦大家帮忙看看为什么会出错:
create trigger chufaqi
before delete
on tb_bm
for each row
begin
declaregonghaobl varchar(20);
declarexingmingbl varchar(10);
declarexingbiebl varchar(20);
declarenianlingbl smallint(8);
declareruzhishijianbl varchar(30);
declaredizhibl varchar(30);
declaredianhuabl varchar(4);
declarezcsm varchar(4);
declarepdts int;
selectcount(*) into pdts from tb_yg where bmbh=old.bmbh;
declareyoubiao cursor for selectgonghao,xingming,xingbie,nianling,ruzhishijian,dizhi,dianhua,shuoming fromtb_yg where bmbh=old.bmbh;
openyoubiao;
repeat
ifpdts>0 then
fetchyoubiao into gonghaobl,xingmingbl,xingbiebl,nianlingbl,dizhibl,dianhuabl,zcsm;
insertinto lizhixinxibiaovalues(gonghaobl,xingmingbl,xingbiebl,nianlingbl,dizhibl,dianhuabl,zcsm);
endif;
endrepeat;
closeyoubiao;
deletefrom tb_yg where bmbh=old.bmbh;
end//报错:
|