Mārcis Liepiņš Mārcis Liepiņš - 7 months ago 18
SQL Question

Updating values with another table values using trigger

I have two tables!

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


I want to create a trigger that updates table NF0 if in table PID is inserted new values! I wrote this trigger upd:

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


,but it updates it like this!

NF0

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


, but I want output to look like this

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


What I am writing wrong?

Answer

This works:

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)

From the OP's comments it got clear that the table PID gets filled by another trigger. So the trigger upd must be called after that other trigger. Let's assume the other trigger is called fill_pid, then

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 ;

will do.