Accessing ORA_ROWSCN in the triggers body

Mostly Copy/Pasting my response from this question on stackoverflow

Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).

Some things I'd still use triggers for

• Keeping track of "date_created" and "date_last_edited" fields
• Inserting "ID"'s (in oracle, where there is no auto id field)
• Keeping change history

Things you wouldn't want to use triggers for

• business rules/logic
• anything which connects outside of the database (eg a webservice call)
• Access control
• Anything which isn't transactional ( anything you do in the trigger MUST be able to rollback with the transaction )

Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.