suecnus
asked on
trigger or Transaction
I need to save data to a history table whenever data is entered into one table. Shall I use Transaction to make sure data always goes to history table in the same stored procedure that inserts data to the original table, or is it better to use trigger in the original table to write data to history table? Any ideas would be appreciated.
Dont use trigger, Just insert the data in the history table, about the use of transaction, i dont prefer using a transaction just for History purpose unless it is very worthy
If you are in control of the application, then a trigger would normally seem to be overkill.
I use triggers when I don't have access to change the source of the application (shrink wrapped apps - accounting, payroll, t&a, etc.).
Then the trigger is the only route.
I use triggers when I don't have access to change the source of the application (shrink wrapped apps - accounting, payroll, t&a, etc.).
Then the trigger is the only route.
You are looking for Audit trail and for that purpose I would go for trigger, herewith I have written one small example at my blog, have a look:
http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html
http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html
ASKER
"I use triggers when I don't have access to change the source of the application". Can you explain it in a bit more detail? I can either use trigger in the transaction table to insure data always inserted to the history table before it is inserted to transaction table. Or I can use transaction to insert data to transaction table and history at the same time. Does whether I have access to application source make a difference? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.