CODEWITHSUNDEEP
mysqlsqltriggers
Mārcis Liepiņš
Mārcis Liepiņš

Reputation: 76

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?

Upvotes: 0

Views: 59

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

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

Upvotes: 0

PerlDuck
PerlDuck

Reputation: 5728

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.

Upvotes: 1

Related Questions