Interesting points, however I would draw a distinction between data integrity, business rules, and UI elements (such as formatting). Experience tells me that whatever is physically allowed in the DB...

Subqueries are usually a bad idea as a first option- how about a left join, perhaps with a DISTINCT in the SELECT clause?

I don't believe SCOPE_IDENTITY() solves the problem, as the initial insert and the audit trigger insert would both be within the context of the same connection.

The only way that I've...