56,677
社区成员
发帖
与我相关
我的任务
分享
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 ;
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=gbk
UPDATE tbl0 SET id = 5 WHERE id = 1 and rid = 233;
[/quote]用这个语句进行了测试,因为你的表中根本没有id=5的记录,自然不会有任何改变。
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=gbk
UPDATE tbl0 SET id = 5 WHERE id = 1 and rid = 233;