触发器建立如下:
DELIMITER $$USE `szs_jihuadan`$$DROP TRIGGER /*!50032 IF EXISTS */ `onDelPickItems`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `onDelPickItems` BEFORE DELETE ON `szs_pick_items`
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @num FROM szs_pick_items WHERE tkdh = old.tkdh;
IF @num<=1 THEN /*如果删除最后一条记录*/
DELETE FROM szs_pick WHERE id = old.tkdh; /*删除表头记录*/
END IF;
END;
$$DELIMITER ;
当我删除szs_pick_items的记录时候,提示错误"can't not update table 'szs_pick' in stored function/trigger because it is already used by statement which invoked this funciton/trigger"但是szs_pick表没有任务触发器的,为什么?实现的功能就是当szs_pick_items的记录删完了,就自动删除szs_pick的记录,2个表的关联字段是tkdh
DELIMITER $$USE `szs_jihuadan`$$DROP TRIGGER /*!50032 IF EXISTS */ `onDelPickItems`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `onDelPickItems` BEFORE DELETE ON `szs_pick_items`
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @num FROM szs_pick_items WHERE tkdh = old.tkdh;
IF @num<=1 THEN /*如果删除最后一条记录*/
DELETE FROM szs_pick WHERE id = old.tkdh; /*删除表头记录*/
END IF;
END;
$$DELIMITER ;
当我删除szs_pick_items的记录时候,提示错误"can't not update table 'szs_pick' in stored function/trigger because it is already used by statement which invoked this funciton/trigger"但是szs_pick表没有任务触发器的,为什么?实现的功能就是当szs_pick_items的记录删完了,就自动删除szs_pick的记录,2个表的关联字段是tkdh
{
if(!$clsPickItems->_delete_record_by_sch($shengchanhao,$link)) //这个就是删除
//szs_pick_items的记录,触发了删除触发器
{
$msg_error = "撤销库提记录失败".mysqli_error($link);
return false;
}
}
这是PHP程序
也没有关联删除啊。。
CREATE TABLE `szs_pick` (
`id` varchar(20) NOT NULL COMMENT '挑库单号',
`szs_schId` varchar(255) default NULL COMMENT '生产号',
`szs_picker` varchar(10) default NULL COMMENT '挑库人',
`szs_date` date default NULL COMMENT '挑库日期',
`szs_state` enum('n','y') NOT NULL COMMENT 'y=已完成挑库,n=未完成实际挑库',
`szs_print` enum('n','y') NOT NULL COMMENT 'n=未打印,y=已打印',
PRIMARY KEY (`id`),
KEY `schId` (`szs_schId`),
KEY `date` (`szs_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8不好意思,触发器刚学习,有点菜
DELETE FROM szs_pick WHERE id = old.tkdh; /*删除表头记录*/你应该是在szs_pick_items 上建了外键吧,你是想在主表删除玩的时候,从表也删除,治理应该有外键的关联吧