Performance impact when creating Audit trail using trigger in MS SQL Server 2012 -

in sql server 2012 database want create audit trail major tables on update , delete operations.noramally creating audit trail using trigger on each table , store on shadow table. there performance impact? if huge records updated or deleted on table. there anyother way implement audit trail?

typically, when implement , audit trail db tables, implement via code, not in triggers. when implemented in code, can provide additional context information, such reason change made, made change, reason behind change, etc., common business requirement. in typical multi-layer application design, have daos each table , business services implement updates responsible calling separate daos core table update , history entry insert. approach no if want bunch of different sources directly making table updates db, it's natural approach if have service-oriented architecture , 1 set of services way , out of tables.

if implement audit trail using approach, of course need make sure audit trail record inserted in same transaction modification core table.

whether perform better trigger-based approach, couldn't say. guess if using bulk insert operations may run faster, slower in more common scenario updating/deleting 1 record @ time via sql. it's option explore, though.