Triggers: counting rows in a bridge table after INSERT



  • 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.



  • 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?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2