Link to home
Start Free TrialLog in
Avatar of suecnus
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.
Avatar of Aneesh
Aneesh
Flag of Canada image

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.

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
Avatar of suecnus
suecnus

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
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial