现有两张表分别为:
CREATE TABLE `business` (
`bs_id` int(10) unsigned NOT NULL auto_increment,
`bsname` varchar(150) NOT NULL,
`comment` varchar(200) default NULL,
`remindtype` int(11) default NULL,
PRIMARY KEY (`bs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;CREATE TABLE `gbmes` (
`g_id` int(10) unsigned NOT NULL auto_increment,
`gb_id` int(11) unsigned default NULL,
`bsname` varchar(150) default NULL,
`remindtype` int(11) default NULL,
`gbname` varchar(100) default NULL,
`opname` varchar(100) default NULL,
`bs_id` int(10) unsigned default NULL,
PRIMARY KEY (`g_id`),
KEY `FK_jz_gbmes` (`bs_id`),
CONSTRAINT `FK_jz_gbmes` FOREIGN KEY (`bs_id`) REFERENCES `business` (`bs_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;其中business表中的主键bs_id是gbmes表的外键,我想当business表中的bsname修改后,gbmes中的bsname也修改,我写了个触发器如下:DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `qdrk`.`trigger_jz_buisness_update`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `trigger_jz_buisness_update` AFTER UPDATE ON `business`
FOR EACH ROW BEGIN
UPDATE gbmes set old.bsname = new.bsname where old.bs_id = new.bs_id;
END;
$$DELIMITER ;不知道怎么的触发器不管用,是我那个地方写错了么?
CREATE TABLE `business` (
`bs_id` int(10) unsigned NOT NULL auto_increment,
`bsname` varchar(150) NOT NULL,
`comment` varchar(200) default NULL,
`remindtype` int(11) default NULL,
PRIMARY KEY (`bs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;CREATE TABLE `gbmes` (
`g_id` int(10) unsigned NOT NULL auto_increment,
`gb_id` int(11) unsigned default NULL,
`bsname` varchar(150) default NULL,
`remindtype` int(11) default NULL,
`gbname` varchar(100) default NULL,
`opname` varchar(100) default NULL,
`bs_id` int(10) unsigned default NULL,
PRIMARY KEY (`g_id`),
KEY `FK_jz_gbmes` (`bs_id`),
CONSTRAINT `FK_jz_gbmes` FOREIGN KEY (`bs_id`) REFERENCES `business` (`bs_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;其中business表中的主键bs_id是gbmes表的外键,我想当business表中的bsname修改后,gbmes中的bsname也修改,我写了个触发器如下:DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `qdrk`.`trigger_jz_buisness_update`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `trigger_jz_buisness_update` AFTER UPDATE ON `business`
FOR EACH ROW BEGIN
UPDATE gbmes set old.bsname = new.bsname where old.bs_id = new.bs_id;
END;
$$DELIMITER ;不知道怎么的触发器不管用,是我那个地方写错了么?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货