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.
Best Answer
Check the MySQL documentation about the trigger syntax and examples.
You cannot have
AFTER INSERT OR UPDATE
triggers in MySQL. Define 2 triggers, one forINSERT
and one forUPDATE
. Another limitation is thatAFTER
triggers cannot change the updated rows, so you can useBEFORE
triggers. Like this:and similarly for the
BEFORE UPDATE
trigger