Stateful Triggers

When working with database triggers, you might find yourself in a situation where you want to add extra state to your trigger that goes beyond just the fields on the row.

Perhaps you’re looking to add some additional application level state.

For instance, let’s say you wanted a trigger to store the user_id of a user that performed an action within your system but user_id is not a part of the record nor can it be looked up from another table based on that field. You could update the record after it’s inserted but that’s error prone and dependent on you not being forgetful.

An easy way to handle this is to use configuration settings, which allow you to set fields that are globally readable for the duration of the transaction.

It can be set as easily as: SELECT set_config('scope.user_id', 'YOUR_USER_ID', true) during your transaction.

The triggers can then read from SELECT current_setting('scope.user_id', true).