We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

trigger or Transaction

suecnus
suecnus asked
on
Medium Priority
236 Views
Last Modified: 2012-05-07
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.
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Richard QuadlingSenior Software Developer

Commented:
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

Author

Commented:
"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.
Senior Software Developer
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.