一个表联合主键,有时候需要对主键其中的一个字段进行更新,但不生效。
触发器如下:delimiter $$
CREATE TRIGGER updt_trg
AFTER UPDATE
ON tbl0 FOR EACH ROW
BEGIN
UPDATE tbl SET id = new.id, rid = new.rid, name = new.name WHERE id = new.id and rid = new.rid;
END;
$$
delimiter ;
其中,id,rid是联合主键。每次更新tbl0.name字段时,tbl.name则相应地同步更新,但更新tbl0.id时,tbl.id却没有相应地更改。
这个触发器要如何写才能达到我期望的?
触发器如下:delimiter $$
CREATE TRIGGER updt_trg
AFTER UPDATE
ON tbl0 FOR EACH ROW
BEGIN
UPDATE tbl SET id = new.id, rid = new.rid, name = new.name WHERE id = new.id and rid = new.rid;
END;
$$
delimiter ;
其中,id,rid是联合主键。每次更新tbl0.name字段时,tbl.name则相应地同步更新,但更新tbl0.id时,tbl.id却没有相应地更改。
这个触发器要如何写才能达到我期望的?
这个更新tbl0.id 更新成功了吗
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`rid` mediumint(6) unsigned NOT NULL DEFAULT '0',
`name` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;Create Table: CREATE TABLE `tbl` (
`infoID` bigint(20) unsigned NOT NULL DEFAULT '0',
`roleID` mediumint(6) unsigned NOT NULL DEFAULT '0',
`related` smallint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbkUPDATE tbl0 SET id = 5 WHERE id = 1 and rid = 233;
Create Table: CREATE TABLE `tbl0` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`rid` mediumint(6) unsigned NOT NULL DEFAULT '0',
`name` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;Create Table: CREATE TABLE `tbl` (
`infoID` bigint(20) unsigned NOT NULL DEFAULT '0',
`roleID` mediumint(6) unsigned NOT NULL DEFAULT '0',
`related` smallint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbkUPDATE tbl0 SET id = 5 WHERE id = 1 and rid = 233;
Create Table: CREATE TABLE `tbl0` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`rid` mediumint(6) unsigned NOT NULL DEFAULT '0',
`name` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;Create Table: CREATE TABLE `tbl` (
`infoID` bigint(20) unsigned NOT NULL DEFAULT '0',
`roleID` mediumint(6) unsigned NOT NULL DEFAULT '0',
`related` smallint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`rid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbkUPDATE tbl0 SET id = 5 WHERE id = 1 and rid = 233;
用这个语句进行了测试,因为你的表中根本没有id=5的记录,自然不会有任何改变。