Stored procedures for SQL Server things

In the development of the hotel management system, we will create the room table and the room type table (room type table) These two tables, as shown in:

Room Type table: Roomtype

Room Table: Rooms

First, the relationship between the two tables: The table, the Roomtype is the primary table, the two tables have a primary foreign key relationship, Roomtype.rtypeid=room.rtypeid

Analysis of these two tables, we will find such a problem: in the room table and room type table management, when we modify or delete a roomtype table, the corresponding rooms table we also have to make changes and delete , then how do we solve this problem? The first can be set in the database cascade Delete, or the use of triggers can also be resolved 、、、 These methods have some drawbacks, in the actual development, we will take the use of transactions to solve this problem, this method is simple and efficient development, easy to maintain.

Requirements: Delete the roomtype while the room table in the presence of the house type is also deleted.

Idea: When we delete rootype, we find the room type record by Roomtype.rtypeid=room.rtypeid and delete it.

Note: Deleting the entire record is not deleted from the database, the data ID is changed to 1,0 is not deleted.

The following describes the stored procedures that are deleted:

Code:

1ALTER procedure [dbo]. [Usp_deltoomtype]--create a stored procedure that deletes a room type2@rTypeIdint--Room type parameter3  as4BEGIN TRANSACTION--Start a transaction5DECLARE @errorint--Define a variable identification error number6Update RoomtypeSetRtypeisdel=1 where[Email protected]--Delete Room type according to condition, rtypeisdel=1 identity Delete, 0 ID not deleted7   Set@error + =@ @ERROR8   if(@error <>0) --not equal to 0 instructions this sentence execution error9ROLLBACK TRANSACTION--Transaction RollbackTenUpdate theSetRoomisdel=1 where[Email protected]--Delete a room based on conditions One   Set@error + =@ @ERROR A   if(@error <>0) --not equal to 0 instructions this sentence execution error -ROLLBACK TRANSACTION--Transaction Rollback -  Else  theCommit TRANSACTION--Commit Transaction

Finally, the data layer directly calls the method of the stored procedure to complete the deletion of the room type while the room type is also deleted.