trigger to sum all elements for each camp

Question

I have 3 tables pharmacy, have and meds

pharmacy:

id_pharm        name
   1           pharm1
   2           pharm2  
   3           pharm3
   4           pharm4
   5           pharm5

have

fk_id_pharm     amount    fk_id_med
1                40           2
1                 1           3
2                45           1
2                20           4
3                10           1
3                45           3

meds

  id_med            name       
    1               med1
    2               med2
    3               med3
    4               med4
    5               med5

I need to calculate a computed column with a trigger that give me the sum of the amount of all pharmacies so I think I need something like this

 id_med            name          stock   
    1               med1          55      --(45 + 10)
    2               med2          40      
    3               med3          46      --(45 + 1)
    4               med4          20
    5               med5          0

my trigger try

ALTER TABLE meds ADD COLUMN stock INT DEFAULT 0;

DELIMITER $$
 CREATE TRIGGER `trigger3`
 BEFORE INSERT ON `have` FOR EACH ROW BEGIN
UPDATE meds SET stock = (SELECT SUM(have.amount) FROM have INNER JOIN meds ON have.fk_id_med=meds.id_med GROUP BY meds.name);
END $$
DELIMITER ;

but when I try to do a insert in have

insert into have (fk_pharm_id, amount, fk_med_id) values (5,80,5)

I got this:

You can't specify target table 'meds' for update in FROM clause

Answer 1

I think you are looking for:

DELIMITER $$
CREATE TRIGGER trig_have_insert
AFTER INSERT ON `have`
FOR EACH ROW
BEGIN
    UPDATE meds
        SET stock = meds.stock + NEW.amount
        WHERE meds.id_med = NEW.fk_id_med;
END $$
DELIMITER ;

Something is wrong with a trigger that does not refer to NEW or OLD -- the values in the row being modified. I am guessing this is what you want.