Reading back my own comments, I must apologize for my tone. I didn't mean to come off so negative. In fact, I do think your FSM is pretty cool.

So let me explain in more detail how you could solve this without all of the overhead you have created. Assuming a true FSM, there is no need to store the state as a value in your database (e.g. "awaiting_payment" in a [state] field). Instead, you should be storing your states as BIT fields:

[awaiting_payment], [awaiting_shipment], [shipped], etc

where "1" indicates the current state and every other state field is "0". In addition, you will need a nullable timestamp for each state:

[awaiting_payment_timestamp], [awaiting_shipment_timestamp], etc

to store when each state was entered, if at all. In this way, every order is in a single row and your analytics become trivial queries that don't require any complex joins or correlated sub-queries. Additionally, using simple constraints on each state field (e.g. the [awaiting_shipment] field <> 1 if the [awaiting_payment_timestamp] is NULL) would remove the need for your trigger and, again, reduce overhead.

This is called an "accumulating snapshot" table, and it is used in data warehousing to solve the exact problem you are dealing with here - tracking an entity through a series of predetermined events to monitor it's status and mark milestones. Using a "transaction" table like in your example, while certainly possible, is storing your data in a way that you cannot easily answer the questions you may want to ask of it. You are spreading your entity across multiple rows instead of putting it all in one row.

Think about how the data translates to an object in your domain. Let's call this table "order_state" ("order_events" is a bad name for two reasons: it's plural - which is just wrong - and it doesn't faithfully describe your entity). I'm going to assume "order" and "order_item" tables as well. These three tables create an aggregate root that can be used to derive an Order object in your domain. This Order will have, at the very least, 3 properties corresponding to your entity's attributes in your database:

id, items, state

where "items" is a collection of OrderItem and state is OrderState (see where I'm going here?). The question becomes about how the OrderState is populated. If your Order object represents the "context" in your FSM, then the OrderState is the base class for all states:

AwaitingPaymentState, ShippingState, CancelledState, etc

This means polymorphism is inevitable. There are 3 common ways to deal with polymorphism in a database, but using a transaction table (like in your example) is not one of them. For your problem, utilizing "single table inheritance" (where all objects in a hierarchy map to a single table row) makes the most sense because you are not storing different data in each child class, rather, just a marker to indicate which state is current (this is also generally the most performant solution). It also means you don't need to add any additional tables, because your "order_state" table can serve both purposes.

Using a transaction table, you are adding a 3rd orthogonal dimension to your aggregate (another set of primary keys), so your object graph becomes needlessly large and it becomes a chore to choose and hydrate your current state.

My last critique is in regard to the choice of putting your transition logic in the database itself. I just don't see how that can be useful other than to "seem" clean. What I mean is, somehow an order actually has to be shipped or an item has to be paid for, and adding a row to a database table doesn't do that. So somewhere else in your stack you must have duplicated your transaction logic in some fashion in order to facilitate the correct database calls when the events take place in your domain.

For example, using my above objects, when a the "pay" event is raised on your Order object "context", it will invoke the "pay" event on it's current state, AwaitingPaymentState, to undergo transition. In this scenario, there must be some method (an "action") that actually handles the payment. Either there is no state machine at all in your domain and you need to validate the state before processing the payment, OR you have a state machine like my example and you need to transition to the AwaitingShippingState. In either case, the logic is duplicated.

What you have created is a cool academic solution that may work (I don't doubt that it works), but this problem was solved 30 years ago and if this was done on my team, I would insist it be redone to adhere to the basic principals of the entity relationship model and database design.

Does this make more sense?