MySQL Trigger inserts null instead of values

Question

I'm having trouble understanding how triggers execute in MySQL and I'm banging my head on the wall, because I can't seem to find out why it doesn't work.

I have the following trigger

CREATE TRIGGER Insert_Products BEFORE INSERT ON `Products`
FOR EACH ROW BEGIN
    DECLARE x_ProductID INT;
    SET x_ProductID = NEW.`ProductID`;

    SET NEW.`PriceExVAT` = (
        SELECT
            ROUND(p.`Price` * 100 / (100 + v.`VATPercentage`), 2) as priceexvat
        FROM
            `Products` p
        LEFT JOIN
            `VAT` v ON p.`VATID` = v.`VATID`
        WHERE p.`ProductID` = x_ProductID); -- also tried inserting NEW.`ProductID` directly into this line
END $$

However it populates my rows with null instead of the correct values. HOWEVER, putting it in a select query results the correct values. IE:

SELECT
    ROUND(p.`Price` * 100 / (100 + v.`VATPercentage`), 2) as x_value
FROM
    `Products` p
LEFT JOIN
    `VAT` v ON p.`VATID` = v.`VATID`
WHERE p.`ProductID` = 1;

I tried putting it in an AFTER INSERT trigger, but that resulted in a different error. What am I not seeing, how should I fix this?

It seems to be a problem related to inserting new values into a table. `Products`.`ProductID` is INT(11) UNSIGNED NN AI so the value is not inserted. How would I still use this value in a BEFORE INSERT ON trigger? — Adam
– Adam, Commented Aug 15, 2018 at 9:45
From MySQL 8.0 : "In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted." So how would I be able to use an AI value in a trigger? — Adam
– Adam, Commented Aug 15, 2018 at 9:47
You do not need to query the table product (and thus you do not need the id). Use new.vatid to (only) query the vat-table, and new.price and so on to calculate the value. — Solarflare
– Solarflare, Commented Aug 15, 2018 at 9:50
how would i return a single value from the statement then? The productID is required to return a single value — Adam
– Adam, Commented Aug 15, 2018 at 10:48

Solarflare · Accepted Answer · 2018-08-15 12:44:09Z

1

You do not need to query the product-table for the values of the currently inserted row: apart from the autoincrement id, they are provided in NEW, and you can use them directly:

SET NEW.`PriceExVAT` = ( 
    SELECT ROUND(NEW.`Price` * 100 / (100 + v.`VATPercentage`), 2) as priceexvat
    FROM `VAT` v 
    WHERE NEW.`VATID` = v.`VATID`
  )

You can do the same in an before update-trigger.

answered Aug 15, 2018 at 12:44

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

I totally get what you're saying right now yeah! You're right. Thanks for helping me out :)

Collectives™ on Stack Overflow

MySQL Trigger inserts null instead of values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related