dinsdag 4 februari 2014

Why I like PostgreSQL more than MySQL: triggers on foreign key events

Triggers let you... well, "trigger" businesslogic whenever a record's content changes. A common usage for this is auditing changes, or preventing illegal values by triggering a check before accepting the query.

The single reason why you would want to do this things in a trigger is that the trigger will fire whenever a particular action is called on the target table, regardles of how that change was initiated. If your PHP script issues a query the trigger will fire. If you run a query by hand, the trigger will fire. If you update a record which leads to a cascading-update, the trigger will fire.

Except when it's MySQL, because MySQL does not fire triggers if the change is the result of a ON DELETE CASCADE or ON UPDATE CASCADE. So, if you are auditing changes to your shoppingcart table you will never see an entry for records that are removed by a cascading delete on the order table.

PostgreSQL does not suffer from this assenine exception.