Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jamie_pattison
Yak Posting Veteran
65 Posts |
Posted - 2013-04-05 : 11:44:50
|
I have two tables as below:BorrowRatesIDYearIDMinimumValueMaximumValueRateCustomerRateIDCustomerIDBorrowRateIDYieldCommissionAs part of an application, both tables have rows inserted when a customer borrows money. In other words the BorrowRates table insert function returns an ID (Scope Identity). That ID is then used as the BorrowRateID on the CustomerRate table which then has its own insert function which includes info for the remainder fields to complete (from other areas of the app) then insert for CustomerRate.If i wanted to update these two tables how would i be doing it in one attempt? Thanks |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-05 : 11:53:25
|
I think what you are looking for is called a Transaction (http://msdn.microsoft.com/en-us/library/ms188929.aspx). You BEGIN a transaction and update each table. If there are no errors, you COMMIT the transaction. If there was an error, you ROLLBACK the transaction. This ensures that the database is consistant (ACID). |
|
|
jamie_pattison
Yak Posting Veteran
65 Posts |
Posted - 2013-04-05 : 15:02:34
|
Thanks. From my reading so far on SQL Transactions it seems i need an UPDATE statement first before even creating the transaction. So that leaves with me creating the UPDATE statement first but based on the tables above? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-05 : 15:15:38
|
You can't write to more than one table per statement. Well, you can if you insert a partitioned view or use an OUTPUT INTO clause but those are not what you are talking about.What do you mean when you say, "how would i be doing it in one attempt?" ?Lamprey probably interpreted your meaning correctly which I interpreted as you want both inserts to succeed or neither to succeed. All or nothing. Is that correct? If so that means you should put both statements inside an explicit user transaction. A transaction does not require a preceding update statement. Not sure where you got that.Be One with the OptimizerTG |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-05 : 15:18:01
|
I think you are reading it incorrectly. The sequence would be: 1) BEGIN TRAN 2) Do the first update 3) Do the second update 4) COMMIT or ROLLBACK. You may want to wrap the statements in a try/catch block as well. Take a look at examples (B) or (C) on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx . You may want to follow that pattern. One thing to be aware, if it matters to you, is that even if the transaction gets rolled back, if the identity has been incremented that will not revert back. |
|
|
|
|
|