u_person表里有pid(原来的主键,自动增长)字段,现在我需要增加一个id字段,值与pid一模一样,但不改动程序,有没有什么好办法?
其实就是给pid弄个别名叫id问了一下,好像可以用触发器,在插入记录后,更新id=pid
CREATE
TRIGGER `id_pid` AFTER INSERT ON `u_person`
FOR EACH ROW update u_person set id=new.pid 可是在插入数据里mysql提示:
Can't update table 'u_person' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
因为原来的主键是用pid,新程序统一用id做为主键,但又不能立即将pid更改为id,要保持原来程序能正常运行。
其实就是给pid弄个别名叫id问了一下,好像可以用触发器,在插入记录后,更新id=pid
CREATE
TRIGGER `id_pid` AFTER INSERT ON `u_person`
FOR EACH ROW update u_person set id=new.pid 可是在插入数据里mysql提示:
Can't update table 'u_person' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
因为原来的主键是用pid,新程序统一用id做为主键,但又不能立即将pid更改为id,要保持原来程序能正常运行。
TRIGGER `id_pid` AFTER INSERT ON `u_person`
FOR EACH ROW set new.id=new.pid 如上即可。
TRIGGER `id_pid` AFTER INSERT ON `u_person`
FOR EACH ROW set new.id=new.pid 修改本行的字段值就行了。不能在触发器里再修改自己,要不就会循环触发,那就死翘了。
执行你这个的时候出现
#1362 - Updating of NEW row is not allowed in after trigger
CREATE
TRIGGER `id_pid` before INSERT ON `u_person`
FOR EACH ROW set new.id=new.pid
UPDATE触发的BEFORE可以访问NEW,AFTER可以访问OLD。
pid变,id也变。
TRIGGER `id_pid` AFTER INSERT ON `u_person`
FOR EACH ROW set new.id=OLD.pid
CREATE
TRIGGER `id_pid` before INSERT ON `T_1`
FOR EACH ROW
set new.id1=(select if(isnull(id),1,id+1) from (select max(id) as id from T_1) tmp);
直接用NEW或者OLD都不行。