How to add audit trigger on a list of tables
For audit purpose you may be requested to add ModifyDate and ModifyBy columns for all your tables and create an audit trigger to automatically update that columns as soon as record was updated or inserted.
It is very easy to implement when you have one or two tables, but with 20-30 tables it may take time. But this task can be automated.
In the following script a temporary table created which will contain list of all tables required audit, and later table name and primary key name will be used for new column creation and creation of audit trigger on each table.