#1
  1. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,534
    Rep Power
    595

    Syntax error creating trigger - need new eyes


    I am getting a syntax error trying to create a trigger. I simply cannot see it and need some new eyes.
    Code:
    SET @master_id=6;
    DROP PROCEDURE IF EXISTS update_master;
    DROP TRIGGER IF EXISTS update_master;
    delimiter $$
    CREATE PROCEDURE update_master(IN id INT,IN qty INT)
    BEGIN
            UPDATE product SET quantity=qty WHERE product_id=id;
    END$$
    CREATE TRIGGER update_master ON product AFTER UPDATE
    DECLARE qty,prod_id INT;
    BEGIN
            IF ((NEW.quantity<=>OLD.quantity)=0) THEN
                    SELECT vendor.vproduct INTO prod_id FROM product JOIN vendor
                            ON product.product_id=vendor.vproduct_id
                            WHERE product.model=OLD.model AND vendor.vendor=@master_id;
                    SELECT quantity INTO qty FROM product WHERE product_id=prod_id;
                    CALL PROCEDURE update_master(prod_id,qty+NEW.quantity-OLD.quantity);
            END IF;
    END$$
    delimiter ;
    The error is:

    ERROR 1064 (42000) at line 10: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON product AFTER UPDATE
    DECLARE qty,prod_id INT;
    BEGIN
    IF ((NEW.quantity<=>OLD.' at line 1

    TIA.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,534
    Rep Power
    595
    Never mind. I finally saw the problem.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo