mysql - 使用触发器用另一个表值更新值

Question

我有两张 table !

PID

 P        ID
Jo Te     00001
Mo To     00002
Tim Fo    00003

NF0

 P2        NewID       Team
Jo Te         -       TeamC
Mo To         -       TeamV
Jo Te         -       TeamX
Tim Fo        -       TeamZ

我想创建一个触发器，如果​​在表 PID 中插入新值，则更新表 NF0!我写了这个触发器更新:

delimiter $$
Create trigger upd after insert on pid
 for each row begin
update nf0 set nf0.PID=new.iD ;
 end $$

，但它是这样更新的!

NF0

 P2         NewID       Team
Jo Te      00003       TeamC
Mo To      00003       TeamV
Jo Te      00003       TeamX
Tim Fo        -        TeamZ

，但我希望输出看起来像这样

  P2        NewID       Team
Jo Te      00001       TeamC
Mo To      00002       TeamV
Jo Te      00001       TeamX
Tim Fo     00003       TeamZ

我写错了什么？

Answer 1

这有效:

create table PID (
    P  char(10),
    ID char(10)
);

create table nf0 (
    P2    char(10),
    NewID char(10),
    Team  char(10)
);

insert into nf0 (P2,Team) values
    ('Jo Te',  'TeamC'),    
    ('Mo To',  'TeamV'),    
    ('Jo Te',  'TeamX'),    
    ('Tim Fo', 'TeamZ');

delimiter $$
create trigger upd after insert on PID
for each row
begin
    update nf0 set NewID=new.ID where P2=new.P;
end $$
delimiter ;

select * from nf0;

+--------+-------+-------+
| P2     | NewID | Team  |
+--------+-------+-------+
| Jo Te  | NULL  | TeamC |
| Mo To  | NULL  | TeamV |
| Jo Te  | NULL  | TeamX |
| Tim Fo | NULL  | TeamZ |
+--------+-------+-------+
4 rows in set (0.01 sec)

insert into PID (P,ID) values
    ('Jo Te',  '00001'),
    ('Mo To',  '00002'),
    ('Tim Fo', '00003');

select * from nf0;

+--------+-------+-------+
| P2     | NewID | Team  |
+--------+-------+-------+
| Jo Te  | 00001 | TeamC |
| Mo To  | 00002 | TeamV |
| Jo Te  | 00001 | TeamX |
| Tim Fo | 00003 | TeamZ |
+--------+-------+-------+
4 rows in set (0.00 sec)

<小时/>

从 OP 的评论中可以清楚地看出，表 PID 被另一个触发器填充。因此，触发器 upd 必须在其他触发器之后调用。假设另一个触发器名为 fill_pid，那么

delimiter $$
create trigger upd after insert on PID 
for each row FOLLOWS fill_pid
begin
    update nf0 set NewID=new.ID where P2=new.P;
end $$
delimiter ;

就可以了。