ASP. NET System Development (2"

In databases, most of the tables we face are associated tables. Tables and tables are associated into a "Consortium" through foreign keys ", when we delete data in a data table, the operation fails because of the foreign key. Speaking of this, we should first look at the association between tables.

 

 

 

The three tables shown in are guojia table (country), sheng table (province), and shi table (city ). Between the sheng table and guojia table, the guojia table is the primary key table, and the sheng table is the foreign key table, associate the suoshuguojia field in the sheng table with the id field in the guojia table to associate the two tables, indicating that the sheng belongs to a guojia. Similarly, the suoshusheng field in the shi table is associated with the id field in the sheng table to associate the two tables, indicating that shi belongs to a certain sheng. In this way, the three tables are associated, so we hope that the following error message will appear when deleting a record in the guojia table and deleting the province that belongs to the country together with the city that belongs to the province:

Message 547, level 16, status 0, 1st rows. The DELETE statement conflicts with the REFERENCE constraint "FK_chengshi_guojia. This conflict occurs in the Database "newssystem", table "dbo. sheng", column 'suoshuguojia '. The statement has been terminated.

Anyone who understands the English language can understand that this is obviously a result of a foreign key deletion failure. How can we achieve cascading deletion? Don't worry. I will introduce three swordsmen to help you solve this problem:

 

I. No trace of snow ---- trigger

First, let's take a look at toutiao's resume:

A trigger is a special stored procedure, but it is not called by the outside world as a general stored procedure. It is triggered by an event. There are two types of triggers: instead of trigger and after trigger. There are three trigger events that can trigger the trigger: insert, update, and delete. The After trigger refers to executing the event first when the trigger event occurs, and then executing the preset code. The instead trigger refers to not executing the trigger event when the trigger event occurs, instead, the preset code is executed beyond the trigger event. Next we will create a trigger to achieve cascading deletion of data:

[SQL]
CERATETRIGGER [shanchu]
<Span style = "white-space: pre"> </span> ON [dbo]. [category]
<Span style = "white-space: pre"> </span> instead of DELETE
AS
BEGIN
-- Declare a variable
Declare @ caid int
-- Assign values to variables
Select @ caid = id from deleted
-- Delete a city
Delete shi where id = (selectsuoshusheng from sheng where suoshuguojia = @ caid)
-- Delete Province
Delete sheng wheresuoshuguojia = @ caid
-- Delete country
Delete guojia where id = @ caid
End

Some may ask why we should use the instead of trigger instead of the After trigger? This should start with the cascade deletion mechanism. When we perform the cascade delete operation, it looks down at the first level. For example, we execute the statement delete guojiawhere id = 1 (delete the first level of data) it will automatically go down to check whether there is data in the country's province in Level 2. If not, it will directly Delete the corresponding data in the country table, however, when the second level contains the corresponding data, the data in the first level cannot be deleted due to the role of the foreign key. Likewise, the second level data is the same as the third level data. Therefore, we can draw a conclusion that if there is a foreign key association between tables, the premise of deleting the data at the upper level is that there is no corresponding data at the lower level.

 

Through the above explanation, I believe that you can easily understand why the instead trigger is used. The reason is that the after trigger executes the code we set after the delete operation is executed. However, because of the foreign key, the delete operation is not allowed, since there is no such thing as executing the delete operation, why. The Instead of trigger avoids the above problem. Because the instead of trigger runs the default statement beyond the trigger event, when a delete event occurs, it does not perform the delete operation, instead, execute the following default statement to delete Level 3 data first, then delete Level 2 statements, and finally Delete Level 1 statements. When there is no data at the current level, the data at the upper level can be deleted.

 

Ii. Jiangnan shengshou-Stored Procedure

A stored procedure is a set of SQL statements for specific functions. Compiled statements are stored in the database. When using a stored procedure, you only need to specify the name of the stored procedure and provide corresponding parameters. A stored procedure is a pre-compiled set of SQL statements and optional control flow statements. It is stored in a name and serves as a storage unit. To put it bluntly, a stored procedure is more like a class stored in a database. It encapsulates attributes and methods in the class, you only need to give it a parameter to execute the corresponding function. If you want to summarize its advantages, it can be divided into the following points:

 

1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

 

2. when performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.

 

3. The stored procedure can be reused to reduce the workload of database developers.

 

4. High security. You can set that only one user has the right to use the specified stored procedure.

Trigger trigger can be seen as a disciple of the stored procedure. Since the apprentice can easily perform cascading deletion, let's take a look at how the master Master handles cascading deletion:

[SQL]
<Span style = "font-size: 12px;"> create procedure [dbo]. [shanchu]
-- Declare an integer variable caid
(@ Caid int)
AS
BEGIN
-- Delete a city
Deleteshi where suoshusheng in (select id from sheng where suoshuguojia = @ caid)
-- Delete Province
Deletesheng where suoshuguojia = @ caid
-- Delete country
Deleteguojia where id = @ caid
END </span>

When we execute this stored procedure, we only need to call the stored procedure and provide the parameters. The format is exec + stored procedure name + parameter. By comparing the trigger and stored procedure, we can find that the essence of the trigger has not changed much. The difference can be roughly classified as two points: first, the position and form of the variable Declaration have changed. The second is to use the process. The trigger is passive when the event is triggered, and the stored procedure is called actively.

 

Iii. Undercover implicit man-constraint itself

This is the most depressing thing in the system. SQL Server 2005 and later versions support cascading deletion of data tables. All we need to do is to set the link. Because I didn't find this function at first, I had to use stored procedures and triggers, whether it was hidden too deep or my discovery was too weak, so annoying. Now, we will teach you how to easily Delete cascading:

1. Create a database relational table and add a data table that requires a foreign key Association

2. Create a foreign key relationship between the primary key of the corresponding primary key table and a field of the foreign key table. The following page is displayed:

 

3. Set the deletion rules to "cascade" according to the oval, and set the tables for which foreign key associations are to be established in sequence as needed. The deletion rules can be completely deleted no matter how many levels of data are available.

Author: a1314517love