How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control?
Or are you actually typing in triggers manually in the Postgres command line?
Rails migrations are checked in to version control, and then rails keeps track of which "migration" has been run. For tables this works fine, because you want to define them only once and if you need to change columns/add columns etc. you can create a new migration, which contains only the updates you want.
For triggers however you need to replace the trigger wholesale, so you end up with several versions of the trigger on your file system and need to check each migration file to see what the latest definition of the trigger is (for tables rails normally creates a separate file with the current state of the database after running a migration, so you have relatively easy access to the latest state of table definitions).
You can easily add functions, triggers, etc. as SQL in migrations. It’s just not a good idea when you realize you might want to change them. Putting them in migrations seems nice at first because it omits an extra step when upgrading your db. But then you have the SQL multiple times in your migrations, which makes finding the correct version annoying.
Not the OP but I did use triggers as suggested in the past. IME the issue is that DDL like triggers are transactional, so when you deploy them executing queries could be using a mix of old and new.
Granted, this is an issue for any system comprised of distributed components. Though I think the pain is more acute when it's the source of truth.
One way around is downtime or going all in on stored procedures for all work, or at least all writes.
The other wrinkle is that the trigger has to be rolled back when reverting code. And some shops are not diligent about maintaining down procedures. There is also the possiblity of rolling back migrations out of the order they were run, so you might not get back to a consistent state.
To keep things clean one could keep the trigger code in a file separate from any migrations and then create a migration that references the trigger source so that one doesn't have to go digging through the migration files later to read the trigger or update it.
I think some folks above may literally have meant they put the trigger code in the migration file itself. Or I'm way off base.
How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control?
Or are you actually typing in triggers manually in the Postgres command line?