Triggers

[Srirama]

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

[Darryl Caillouet]

Instead of setting the variables using a trigger on the insert, can you just set the default values of the fields to getdate() and current_user? If you leave these fields undefined on the initial insert, SQL Server will insert the default value in their place.

Then you would only have to use a trigger on the update.


------------
Srirama at 8/29/01 9:40:54 AM

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

[rich]

On the inserts, just set the column defaults to Current_user and getdate() and mark as NOT nULL and they will auto fill in, no need for trigger.

Not sure of a better way to do the update, but it looks ok to me.


------------
Srirama at 8/29/01 9:40:54 AM

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