This chapter is from the book
This chapter is from the book
This chapter is from the book
Changing What Is Already Stored
You alter data through the use of UPDATE. Just as with the input of data, any alterations made to the data are subject to the rules and constraints defined in the table schema. Any data modifications made that do not meet these defined standards result in errors that the front-end application may have to trap and control.
Updating a Single Record
There are three different techniques for updating a single record:
- Filtered update— This technique is no different than changing a number of records. The difference is that the criteria used need to be something unique, such as a primary key or an identity.
- Positioned update— This technique is a programmatic solution. Essentially, you set up a procedure that scrolls through the data. When the record pointer (that is, cursor) is on the record to be updated, you issue the UPDATE command with the WHERE CURRENT OF clause.
- Computed column— You can update a single record by using a computed column. In this case, when the computation involves a field whose value changes, the result of the computation is affected and the field will change.
Doing Updates That Affect Multiple Records
Most data modifications are based on a conditional operation or are in-place updates of the current record. The following is a basic UPDATE statement that adjusts data based on the criteria given:
UPDATE Customers
SET Region = 'South America'
WHERE Country IN ('Argentina', 'Brazil', 'Venezuela')
Obviously, an update performed with a broad condition will affect a number of records. Records in other tables could be affected. In the event of cascading updates, if a referenced row has its key altered, those changes would ripple down to the related rows of the subsidiary table.
Performing Transaction Processing
Updating data within a transaction gives you a chance to undo changes before they are actually saved to the database. This usually comes into play when there are a number of steps needed for the data alteration to be valid. If any of the steps within the process fails, you can roll back everything that has been done, which leaves the system as if no change had ever occurred.
The key to handling transactions is to know when to commit the change and when to roll it back. You usually execute the ROLLBACK statement as part of a conditional operation that checks for lack of success in the process. If an error occurs in a transaction, you roll back the transaction. If the user decides to cancel the transaction, you roll back the transaction. If a portion of a required process cannot be completed, you roll back the transaction.
A ROLLBACK statement backs out all modifications made in the transaction. This statement returns the data to the state it was in at the start of the transaction. When errors occur, you should use COMMIT or ROLLBACK. You use COMMIT if you want to ensure that changes are saved to the point of the error, and you use ROLLBACK to cancel all changes.
One effective tool for handling errors, including those in transactions, is the new T-SQL TRY/CATCH construct, illustrated here:
BEGIN TRY
-- Attempt the desired processing
COMMIT
END TRY
BEGIN CATCH
-- An error occurred, handle it or
ROLLBACK
END CATCH;
GO
Normally, ROLLBACK would occur during the CATCH operation; however, if desired, a COMMIT could be coded there as well to save the changes instead of abandoning the operation.
Upgrading Data from Previous Releases of SQL Server
If you have worked with SQL Server for a while and are comfortable with the four-part naming convention for addressing objects, you will be comfortable working with the new object schemas, which also uses a four-part name for addressing objects:
Server.Catalog.Schema.Object
The Schema portion historically was addressed as the object owner. All objects on previous instances of the server were generally owned by dbo. That makes the most common schema for upgraded databases the dbo schema. For comparison purposes, you can visit the Microsoft website, at www.microsoft.com/sql/downloads/2005/default.mspx, and install the sample databases Pubs, Northwind, and AdventureWorks and compare the object naming conventions.