As with any other action against a database, deleting rows may end with errors. An error may range from a simple cant delete announcement to a catastrophic server failure. The level and severity values for errors have been discussed previously. The following information involves the specifics of delete actions.
Based on a discussion earlier in this chapter, you are familiar with what happens when you try to delete data using a WHERE clause that has no matches within the table: absolutely nothing happens. The delete instruction performs the action, but the command has nothing to do because there are no rows that match the WHERE clause. However, this fact might go unnoticed because Microsoft SQL Server 2005 does not interpret it as an error.
Execute the sentence that follows :
DELETE FROM [AdventureWorks].[Sales].[SalesReason] WHERE SalesReasonID=100
You will receive the message below because there is no row in the SalesReason table with Id equal to 100.
(0 row(s) affected)
If you base your application development on trapping errors in your database actions, you must add your own error for this action. You can add your own error and error messages to SQL Server 2005 to raise specific information by using the sp_addmessage stored procedure.
Sp_addmessage takes the following parameters shown in Table 11-1.
Integer identifying your message. This value must be between 50,001 and 2,147,483,647. It is a good practice to use values in a defined range for each application.
Severity level. If you create your own error for something that your code or the user can solve, you should specify a value between 11 and 16.
Text to display when this error is raised
Language used in the msgtext. This allows you to add multiple messages for the same error, depending on the users language. If this value is NULL, then the default language of your actual connection session will be used.
If TRUE, the error will be written into the Application log when it is raised.
The following example uses a number greater than 50,001 for the message.
sp_addmessage 51001,16,'No rows deleted','us_english',FALSE
You should always use stored procedures to delete rows and raise the error when nothing has been deleted.
ALTER PROCEDURE [Sales].[SalesReason_DeleteChecked] @SalesReasonId int, @CheckStamp bigint, @DeletedRecords int=null OUTPUT AS BEGIN DELETE Sales.SalesReason WHERE SalesReasonID=@SalesReasonId AND cast(Lastversion AS bigint)=@CheckStamp SET @DeletedRecords=@@ROWCOUNT -- Raise an error when no row was deleted IF @DeletedRecords=0 RAISERROR (51001,16,1) END
This is probably the most common error you will encounter during the life of your applications. You must therefore trap the error to manage it appropriately.
Execute the following code:
DELETE FROM [AdventureWorks].[Sales].[SalesTerritory] WHERE territoryId=1
You will receive the following error:
Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeader_Sales Territory_TerritoryID". The conflict occurred in database "AdventureWorks", table "Sales.SalesOrderHeader", column 'TerritoryID'. The statement has been terminated.
Notice that this is a Level 16 error, indicating that the user can solve the problem. With this level, you can trap the error in your own application code. Because the user needs to understand the mistake, you can provide clearer information than the information provided by SQL Server 2005 (which is more technical information).
As in any SQL Server sentence, you may receive other errors, such as syntax or system failure errors. You must manage each error properly. Syntax errors will be a Level 16 error, which is the same level as referential integrity errors or the errors you created for DELETE sentences that do not affect any records.