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.
AFTER INSERT OR UPDATE
triggers in MySQL. Define 2 triggers, one forAFTER INSERT
and one forAFTER UPDATE
.