create table Domains(domain_id int not null auto_increment primary key,
domain_name nvarchar(50) binary not null,
domain_priority int,
domain_note text,
domain_father_id int,
foreign key(domain_father) references Domains(domain_id)
);使用以上代码创建一个表,其实就是为了实现一个部门有一个上级部门的需求,这么弄之后出现问题,使用以下存储过程:
delimiter // ;
create trigger t_domain_bd before delete on Domains
for each row
begin
update Domains set Domains.domain_father_id =null where Domains.domain_father_id =OLD.domain_id;
end;
//然后,删除该表中某字段
delete from Domains where Domains_id =5;
的时候就会出现错误:
ERROR 1442: Can't update table 'Domains' in stored function/trigger because it is already used by statement which invoked the ... is stored function/trigger.请问为何会如此,我是不是还要定义一个部门和其上级部门的关系表啊
domain_name nvarchar(50) binary not null,
domain_priority int,
domain_note text,
domain_father_id int,
foreign key(domain_father) references Domains(domain_id)
);使用以上代码创建一个表,其实就是为了实现一个部门有一个上级部门的需求,这么弄之后出现问题,使用以下存储过程:
delimiter // ;
create trigger t_domain_bd before delete on Domains
for each row
begin
update Domains set Domains.domain_father_id =null where Domains.domain_father_id =OLD.domain_id;
end;
//然后,删除该表中某字段
delete from Domains where Domains_id =5;
的时候就会出现错误:
ERROR 1442: Can't update table 'Domains' in stored function/trigger because it is already used by statement which invoked the ... is stored function/trigger.请问为何会如此,我是不是还要定义一个部门和其上级部门的关系表啊
domain_name nvarchar(50) binary not null,
domain_priority int,
domain_note text,
domain_father_id int,
foreign key(domain_father_id) references Domains(domain_id)
);1,建表语句有问题2.请问LZ这个外键在这里有意义么3.你的触发器对本表进行操作 在mysql中是不允许的
1.建表
create table Domains(domain_id int not null auto_increment primary key,
domain_name nvarchar(50) binary not null,
domain_priority int,
domain_note text,
domain_father_id int);2.建立辅助表
create table fuzhu(domain_id int);3.建立触发器
create trigger t_domain_bd before delete on Domains
for each row
insert fuzhu values(old.domain_id);
4.建立一个存储过程 它是你删除后再执行的
delimiter //
create procedure usp_up()
begin
truncate table fuzhu;
update Domains d,fuzhu f
set d.domain_father_id =null
where d.domain_father_id =f.domain_id;
end;
//
delimiter ;这样你进行完删除操作后 允许这个存储过程 就可以做到你的需求了