I feel like audit trails are under-discussed and under-rated.
Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't?
Like, just creating a history of every table
seems a little wasteful to me, but you're probably
talking more nuanced than that
Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here)
What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables.
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();
Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.