0

I'm teaching myself RDMSs through a text book. I'm used to using MySQL so I'm sticking to it, but the book uses Oracle.

I'm trying to create a trigger in MySQL with the Oracle command

CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT
BEGIN
    UPDATE ON PRODUCT
        SET P_REORDER = 1
        WHERE P_ONHAND <= P_MIN;
END;
/

When I run the above, I get

Error code 1064, SQL state 42000: 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 'TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT'

Basically in the PRODUCT table, if an INSERT or UPDATE is made, and the quantity on-hand (P_ONHAND) is less than the product minimum (P_MIN), a flag should be raised on P_REORDER.

I'm not sure how to go about putting this into MySQL syntax without separating the INSERT OR UPDATE, which would break the requirement of the command. Is this even possible in single TRIGGER with MySQL?

This is the best I can think of after going through the MySQL manual

delimiter //
DROP TRIGGER TRG_PRODUCT_REORDER;
CREATE TRIGGER TRG_PRODUCT_REORDER 
AFTER INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < P_MIN THEN
            SET P_REORDER = 1;
        END IF;
    END; //
delimiter ;

But I still get the error

Error code 1064, SQL state 42000: 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 'CREATE TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE ON PRODUCT FOR EACH R' at line 2'

Any help would be appreciated.

2
  • 1
    You can't have AFTER INSERT OR UPDATE triggers in MySQL. Define 2 triggers, one for AFTER INSERT and one for AFTER UPDATE. Mar 5, 2014 at 10:50
  • @ypercube yea I was starting to figure that after the million different attempts I made. Thanks. Mar 5, 2014 at 10:54

1 Answer 1

3

Check the MySQL documentation about the trigger syntax and examples.

You cannot have AFTER INSERT OR UPDATE triggers in MySQL. Define 2 triggers, one for INSERT and one for UPDATE. Another limitation is that AFTER triggers cannot change the updated rows, so you can use BEFORE triggers. Like this:

delimiter //
DROP TRIGGER IF EXISTS TRG_PRODUCT_REORDER_BEFORE_INSERT ;
CREATE TRIGGER TRG_PRODUCT_REORDER_BEFORE_INSERT
BEFORE INSERT ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < NEW.P_MIN THEN
            SET NEW.P_REORDER = 1;
        END IF;
    END; //
delimiter ;

and similarly for the BEFORE UPDATE trigger

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.