I am having small problem and please tell me the best solution for this.
we want to impliment audit columns to all the tables in database.
example:
Tables : Order_id Int,Orderdate datetime,Amount int,
User_created varchar(20),Date_created datetime,
User_updated varchar(20),Date_updated datetime

User_created,Datecreated,user_modified and Date_modified columns will be there on all tables in the database.Now i want to update this foour fields depends on the action.I wrote triggers for this.But i am not sure how good these triggers work with highly OLTP system.Here I am writing this triggers please tell me the best way od doing it from the database side.

Insert Trigger

CREATE TRIGGER [InsertAudit] ON [dbo].[Orders]
FOR INSERT
AS
rollback transaction
insert into Orders(orderid,orderdate,amount,date_created,user_ created,date_modified,user_modified) select orderid,orderdate,amount,current_user,getdate(),cu rrent_user,getdate() from inserted


Update Trigger

CREATE TRIGGER [UpdateAudit] ON [dbo].[orders]
FOR UPDATE
AS
update orders set user_modified=current_user,date_modified=getdate() where orderid= (select orderid from inserted)


Is there any other way of doing this.I know this is very expensive on OLTP.Our system is highly OLTP.12000 orders per hour and 3 million db transactions a day.Please advise me on this


Thanks
Srirama