1

I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using JPA, new rows are automatically inserted in the bridge table Order_Products.

When in the Trigger I use

    SET @numOfOPs = (
        SELECT COUNT(IDOrder) 
        FROM Order_Product 
        WHERE IDOrder = NEW.ID)

on new entries, the count on Order_Procut seems to return 0 (or the value before the insert on Order).

Here the signature of the Trigger:

    CREATE TRIGGER `Order_AFTER_INSERT` 
    AFTER INSERT ON `Order` 
    FOR EACH ROW

What could be the problem? I will add additional information if needed.

1 Answer 1

0

Don't you mean

( SELECT COUNT(*) FROM Order_Product
       WHERE ID_Order = NEW.ID_Order )

What will you be doing with @numOfOPs after the Trigger?

3
  • Well I tried both with and without IDOrder in the COUNT() but nothing changes. My need is to use @numOfOPs in an INSERT on a statistics table, in order the have directly the count without additional queries. May the problem be the fact that INSERT on Order happens before the INSERT on Order_Products? Commented Feb 13, 2022 at 15:12
  • @FilippoScaramuzza - My Answer focuses on NEW.ID_Order versus NEW.ID.
    – Rick James
    Commented Feb 13, 2022 at 20:08
  • Actually the field of the table Order is ID, not ID_Order. Commented Feb 13, 2022 at 21:49

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.