August 27, 2009 at 6:57 pm
I have a web page that lets the user key in data to update table1(EnterpriseScheduleDefaults).
The updated values on table1(EnterpriseScheduleDefaults) are to be propagated to table2(CustomerScheduleDefaults) and to table3(CustomerOrderScheduleDefaults).
One update on table1 can result in multiple row updates on table2 and table3.
Currently table2 has 3 millions rows and table3 has 300,000 rows.
1. Is it a good idea to use after triggers in this scenario?
Assuming triggers implementation is the way to go...
2. Is the user going to clock for the time it takes to update one row on table1? or is the user going to clock for as long as it takes to update one row on table1 + the time it takes for updates on table2 and table3?
3. Can updates on table2 and table3 be fired concurrently(concurrent triggers?)
4. How to scope the transaction? If updates on either table2 or table3 fail, rollback update on table1? Or update on table1 and have triggers as fire and forget events?
It is hairy because the updates on table2 and table3 will be time consuming ( ie not in terms of seconds and it is not acceptable to have an online user clocks for more than 30 secs) but I do want to follow
the “all or nothing” rule. An update on table1 is considered successful from a business standpoint only and only if updates on table2 and table3 are completed successfully as well.
I am thinking of a process to let user submit a request for table1 update. Then a background job will perform table1 update firing the triggers to update table2 and table3 as one unit of work.
Thank you much in advance for any suggestions and input.
August 28, 2009 at 7:21 am
Okay, the main issues with triggers, in my opinion are:
2. Triggers do lengthen the transaction as they are part of the original insert/update/delete transaction. This can be a positive or negative. In your case it appears to be a positive as you want an all or nothing transaction.
I typically only use triggers for auditing/history purposes. In your situation I would recommend handling this in the business layer wrapping all 3 actions in a single transaction. You could do this with ADO.NET or you can do that in a stored procedure that does all 3 inserts. In theory, if you have the right indexes the "cascaded" updates should be very fast.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2009 at 9:30 am
Thank you Jack for your input. I agree with you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply