Need help with trigger php/mySQL

I need to make this trigger work using three tables. Does anyone see a problem?

The 'qty' needs to always show the latest q开发者_运维技巧uantity from the adds and pulls.

CREATE TRIGGER Upd_Cartons_Qty 
AFTER INSERT ON cartons_added FOR EACH ROW
BEGIN
UPDATE cartons_current SET qty = qty + NEW.add_qty WHERE part_no = NEW.part_no;
END;



TABLE NAME:  cartons_current
+--------------+--------------+-------+-------+
| Column       | Type         | Null  | Key   |
+--------------+--------------+-------+-------+
| part_no      | varchar(20)  | No    | Prim  |
| qty          | int(8)       | No    |       |
+--------------+--------------+-------+-------+

TABLE NAME:  cartons-added
+--------------+--------------+-------+-------+
| Column       |  Type        | Null  | Key   |
+--------------+--------------+-------+-------+
| part_no      | varchar(20)  | No    | Prim  |
| add_qty      | int(8)       | No    |       |
+--------------+--------------+-------+-------+

TABLE NAME:  cartons_pulled
+--------------+--------------+-------+-------+
| Column       | Type         | Null  | Key   |
+--------------+--------------+-------+-------+
| part_no      | varchar(20)  | No    | Prim  |
| pull_qty     | int(8)       | No    |       |
+--------------+--------------+-------+-------+

---

1- You cannot use ; as a final delimiter for the end. You need to set a delimiter before the trigger.
2- A after insert trigger should logically have a prefix ai, not upd.
3- You cannot change values in a after trigger in the same table the trigger is for. So if you (might) need to change values in cartons_added you need to do that in the before trigger.
4- On the other hand, you cannot change values in other tables in a before trigger, because these changes might rollback and then you have inconstancy i your tables, so that need to happen in the after trigger.
5- You can effect multiple tables in a trigger, just do it like the example.

DELIMITER $$

CREATE TRIGGER ai_Cartons_Qty AFTER INSERT ON cartons_added FOR EACH ROW
BEGIN
  UPDATE cartons_current SET qty = qty + NEW.add_qty WHERE part_no = NEW.part_no;
  UPDATE cartons_pulled SET x1 = x1 + NEW.add_qty WHERE part_no = NEW.part_no;
END$$

DELIMITER ;

If you want to alter some value in the triggers own table, don't use update, use code like below instead:

DELIMITER $$

CREATE TRIGGER ai_Cartons_Qty BEFORE INSERT ON cartons_added FOR EACH ROW
BEGIN
  -- Update cartons_added .... will not work.
  -- Use SET NEW.fieldname instead.
  IF NEW.qty_added = 0 THEN 
    SET NEW.qty_added = 1;
  END IF;
END$$

DELIMITER ;