Dealing with Errors

The update process involves the same errors as those explained in previous chapters. You might try to update a row that no longer exists. This will not be considered an error from the database engine point of view; it will simply return a 0 rows affected message. Refer to Chapter 11 for more details on how to handle this situation.

Some special errors that occur during update operations are detailed in the following section.

Assigned Value Is Higher than the Field Datatype Limit

This error may appear when you update rows with calculations. Using stored procedures can protect you from this error. A stored procedure receives the values through parameters, which have their own datatypes. The overflow will occur as soon as you try to assign a datatype that could contain a higher value to a parameter.

Execute the following update. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample09.sql.

 UPDATE [AdventureWorks].[Production].[Product]    SET [ReorderPoint] = ReorderPoint *2^128 

You obtain an overflow error similar to the following message:

 Msg 220, Level 16, State 1, Line 1 Arithmetic overflow error for data type smallint, value = 48128. The statement has been terminated. 

Column Value Violates Referential Integrity

An application usually allows the user to choose from the actual values to update a column that uses referential integrity. However, it is possible for another user to remove one of these choices between the read and the update calls. That is why you must check the integrity error each time you update a row. You can obtain the error by executing the following code. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample10.sql.

 UPDATE [AdventureWorks].[Production].[Product]    SET [ProductSubcategoryID] = 0  WHERE ProductID=1 

You will receive an error similar to the following message:

 Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Product_ProductSubcatego ry_ProductSubcategoryID". The conflict occurred in database "AdventureWorks", table "Product ion.ProductSubcategory", column 'ProductSubcategoryID'. The statement has been terminated.