6

Database is MySQL. I am looking to implement a trigger that updates an aggregate rating table whenever a new rating is placed. Then there are some other similar use cases where I'm looking to implement triggers.

These triggers may be updating federated tables. So my question is: whether a php script that inserts a new rating will need to wait for the trigger to complete before the php script completes?

PS: I understand triggers is not a good idea to put logic, and may be I would be better off with some messaging such as RabbitMQ.

jeff musk
  • 1,032
  • 1
  • 10
  • 31
  • 1
    My intuition would tell me that no, you don't have to wait for the trigger to run, but you do if you want to run another query on that connection. That said, I have never used triggers so I can't post this as an answer. – Niet the Dark Absol Feb 04 '12 at 22:52
  • I assume you mean an AFTER INSERT trigger? – The Nail Feb 04 '12 at 23:02
  • If you want asynchronous update of the aggregation tables then a cron script, or a messaging system gearman/rabbitmq/zeromg to execute an update script would be a better solution. – Mark Baker Feb 04 '12 at 23:22
  • @MarkBaker can you please look at a follow up question related to your suggestion http://stackoverflow.com/questions/9151698? Thanks – jeff musk Feb 05 '12 at 18:22

1 Answers1

14

Triggers are synchronous; the INSERT/UPDATE/DELETE that spawned them blocks until the trigger completes executing, including any actions the trigger executes (and even any subsequent triggers spawned by those actions).

Triggers are atomic. That is, if any change executed by the trigger fails, then the trigger fails, and it also cancels the change that spawned the trigger.

Triggers also make changes in the scope of the same transaction that spawned the trigger. So changes made by the trigger can be committed/rolled back along with the changes that spawned the trigger. If triggers kept running while your PHP app finishes its transaction and exits, then there would be no way to ensure they happen in that transaction.

This applies both to BEFORE triggers and AFTER triggers. Your app blocks until all triggers are finished.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828