SQL Server trigger tutorial

Main Category Table Name: Navtion_TopSubject primary key fTopID Char (36)
Sub-category table name: the foreign key of Navtion_NodeSubject is the same
Content table name: The tText foreign key is the same as above
Copy codeThe Code is as follows:
// ------------- Code start --------------------
Create trigger [RemoveTopAndNodeText] ON [dbo]. [Navtion_TopSubject]
INSTEAD OF DELETE
AS
/* Define the variables used by the trigger */
DECLARE
@ FTopID Char (36 ),
@ FNodeCount Int,
@ FTextCount Int,
@ FTopName VarChar

/* Assign the transmitted fTopID key value to the @ fTopID variable */
/* Start transaction */
Begin tran Remove_TopSubject
Set @ fTopID = (Select fTopID From deleted)
Set @ fTopName = (Select fTopName From deleted)
/* Save the point before deletion to prevent errors */
Save Tran my_Save1
/* First, determine whether the Sub-Table NodeSubject contains content */
Set @ fNodeCount = (Select Count (*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @ fTopID)
If @ fNodeCount> 0
Begin
/* Determine whether the tText table contains the content */
Set @ fTextCount = (Select Count (*) From tText Where tText. fTopID = @ fTopID)
If @ fTextCount> 0
Begin
Delete From tText Where tText. fTopID = @ fTopID
Delete From Navtion_NodeSubject Where fTopID = @ fTopID
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End
Else
Begin
Delete From Navtion_NodeSubject Where fTopID = @ fTopID
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End
End
Else
Begin
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End
If @ Error = 0
Commit Transaction
Else
Begin
Rollback Transaction my_Save1
Raiserror ('deletion error, record: % s and its content are not deleted. ', 16,1, @ fTopName)
End
// ------------------ Code ended ---------------------

I. background
This is an article processing system I wrote. The classification level is two levels, that is, there are two levels of directories similar to this:
News
--> Domestic news
--> International news
Tutorial
--> Asp tutorial
--> C # tutorial
--> Jsp tutorial
Download
--> Tool download
--> Source code download
Each article belongs to one of the above directories.
In this way, the database must have three tables: Master category table, secondary category table, and article content table. In addition, to ensure data integrity, I use the relationship (understanding SQL Server). In this way, if there are subcategories in the news class and there are articles under each subcategory, if you use the Deltee statement to delete a class, an error occurs because it violates the data integrity constraints. After you delete the class, the record will become a dead record. Therefore, you must ensure that all the sub-categories and content contained in this category are deleted when you delete the records in the master table. The trigger I wrote is used to delete the trigger of the primary table category. You can delete the records of the primary table if there is content in the primary table category.
Ii. Database Structure
1. Main Category Table
Table Name: Navtion_TopSubject
MASTER: fTopID data type: Char size: 36
Category name: fTopName data type: VarChar size: 30
2. Secondary category table
Table Name: Navtion_NodeSubject
Primary Key: fNodeID data type: Char size: 36
Foreign key: fTopID data type: Char size: 36 (indicates the primary category of the record)
Category name: fNodeName data type: VarChar size: 30
3. Content table:
Table Name: tText
Primary Key: fID data type: Char size: 36
Foreign key 1: fTopID points to the main category table to indicate the main category
Foreign key 2: fNodeID points to the sub-category table, indicating the sub-category
3. Procedure
1. This trigger is placed in the Navtion_TopSubject table of the main category. The trigger condition is the Delete statement. If the Delete command is executed on this table, the code is triggered.
2. Define the trigger to accept the passed Delete statement, and then execute the code according to this statement.
3. The standard SQL deletion code is as follows:
DELETE From Navtion_TopSubject Where fYopID = 'aaa' (assume that the main category to be deleted is news and the primary key number is aaa)
4. In this way, we can use this aaa to find whether the sub-category table and content table have content.
5. Using the deleted table, the table is logically deleted, which is equivalent to the recycle bin in Windows. SQL Server System definition: If a table contains a trigger, any operation is not performed directly, but a logical operation. This operation is performed in the Inserted Table (insert), Updated table (update), and Deleted table (delete. Take the Deleted table as an example and pass it to the Del command in the SQL Server system. If there is a trigger, SQL first copies the record to be Deleted to the Deleted table (this table is a temporary table, this code can only be used by triggers. It is automatically deleted after the trigger is run:
Select fTopID From deleted
Obtain the content of the primary key that is recorded as aaa in the Navtion_TopSubject table passed to the SQL statement.
6. Store the primary key content in the variable and locate the category table based on the variable. If there is no content, this record does not have any category or article and can be deleted directly.
7. if the sub-category table contains content, use this variable to find the content that belongs to aaa in the content table. If yes, delete the content of the sub-category table. If no content exists, delete the content of the sub-category table.
8. When all records of the category and content tables are deleted, the contents of the primary category table are deleted.
Iv. Process Definition
First, find the number of sub-classes, assign the value to the @ NodeCount variable, and then determine
If the value is greater than 0
Assign a value to @ fTextCount for the tText table based on @ fTopID. If the value is greater than 0
First, delete all records that comply with @ fTopID in the tText table.
Then, delete all content that meets @ fTopID in the Navtion_NodeSubject table of the subcategory table.
Finally, delete all content conforming to @ fTopID in the main category table Navtion_TopSubject.
Otherwise
First, delete all content that meets @ fTopID in the Navtion_NodeSubject subcategory table.
Delete all content that matches @ fTopID in the main category table Navtion_TopSubject.
Otherwise
Only delete all content conforming to @ fTopID in Navtion_TopSubject of the main category table
V. Code explanation
Create trigger [RemoveTopAndNodeText] ON [dbo]. [Navtion_TopSubject]
INSTEAD OF DELETE
AS
The code above is an SQL statement for creating a trigger. content:
Create trigger T-SQL keyword, indicating a TRIGGER
[RemoveTopAndNodeText] is the trigger name.
[Dbo]. [Navtion_TopSubject] indicates the name of the table to which the trigger belongs.
Instead of indicates that the trigger will block the passed SQL commands and turn to the command for executing the trigger.
(In addition to the instead of keyword, there is also the For keyword, indicating that the trigger executes the trigger content after the passed SQL statement is executed, which is generally used in Insert and Update)
DELETE: indicates that the trigger condition of this trigger is the Delete command. If you DELETE this table, the code of this trigger will be executed.
As indicates that the following code is the code of the trigger:
-------------------------------------------------------------------
/* Define the variables used by the trigger */
DECLARE
@ FTopID Char,
@ FNodeCount Int,
@ FTextCount Int,
@ FTopName VarChar
The preceding command defines the variables used by the trigger. The DECLARE command is equivalent to the Dim command of VBScript. However, this statement can define multiple variables without a single command. variables are separated by commas.
@ FTopID is the variable name. SqlServer requires that the @ character must be added before the variable name used in SqlServer.
The variable is followed by the variable type. Char is a fixed-length string, Int is an integer number, and VarChar is a variable-length string.
--------------------------------------------------------------------------
/* Assign the transmitted fTopID key value to the @ fTopID variable */
/* Start transaction */
Begin tran Remove_TopSubject
Set @ fTopID = (Select fTopID From deleted)

/* Save the point before deletion to prevent errors */
Save Tran my_Save1
The above code is explained:
The program annotation is placed between/* and */, similar to the <! -- And -->
Begin tran Remove_TopSubject indicates that the transaction is started, and RemoveTopSubject indicates the transaction name.
A transaction is a mechanism to ensure successful operations. If an error occurs in the transaction, the transaction will be rolled back without affecting the entire system.
For example, if three operations a B c are defined in the transaction, a inserts a record, B deletes a record, and c updates a record. The program starts to execute. If an error occurs in operations B and B after a is successfully executed, the transaction starts to roll back and the that inserts the record will be canceled, returns the status before the three operations are performed.
Set @ fTopID = (Select fTopID From deleted)
The value assignment command of SQL Server saves the value of the variable @ fTopID as the content of the fTopID field in the deleted table.
Set @ fTopName = (Select fTopName From deleted)
The same value Assignment Command assigns the name of the main category to be deleted to the @ fTopName variable, which is used in the subsequent error statements.
Save Tran my_Save1
Save Tran indicates to Save the transaction. If an error occurs, you can use this Save to restore the transaction. It is similar to the storage file in the game. My_Save1 is the storage name, which is equivalent to the storage file name.
Connect:
Set @ fNodeCount = (Select Count (*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @ fTopID)
It is also a variable value assignment statement. Set the number of records in the Navtion_NodeSubject table to which the @ fNodeCount variable stores the records of the master table to be deleted (the content of the @ fTopID variable ).
You can refer to this post to find the statements that meet the conditions of other tables based on the primary table record:
Http://www.dw-mx.com/forum/mb_forum/detail2.asp? F2_id = 37 & f3_id = 9022 & f3_name = Smile Life
--------------------------------------------------------------------
If @ fNodeCount> 0 (mark, level 1 judgment)
Start to judge. If @ fNodeCount is greater than 0, it indicates that the primary category record to be deleted contains a subcategory and cannot be deleted directly. You must first Delete the subcategory.
However, to delete a sub-category, you must delete all content (articles) records contained in the sub-category. Therefore, you must determine whether there is any content record.
-----------------------------------------------------------------
Begin (as a mark, level 1 Begin)
It indicates multiple statements executed under If. If you do not understand the statements, refer to the FAQ post that is not human.
--------------------------------------------------------------
Set @ fTextCount = (Select Count (*) From tText Where tText. fTopID = @ fTopID)
The above code continues to assign values. @ TextCount indicates the number of all content (articles) records contained in the Primary category record to be deleted.
------------------------------------------
If @ fTextCount> 0 (TAG, second-level judgment)
Determine the number of records. A value greater than 0 indicates that a record exists. In this way, you must first Delete the records in the content table, then delete the records in the subcategory table, and finally Delete the records in the Primary category table, this prevents errors.
Begin (mark, level 2 Beging 1)
---------------------------------------------------
Delete From tText Where tText. fTopID = @ fTopID
Delete From Navtion_NodeSubject Where fTopID = @ fTopID
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End (mark, the End command of level 2 Begin 1)
The preceding statement is very simple. First, delete the records whose fTopID complies with @ fTopID in the tText table (delete the content table)
Then, delete the records that match @ fTopID in the Navtion_NodeSubject table (delete the subcategory table)
Finally, delete the records that match @ fTopID in the Navtion_TopSubject table (delete the main category table)
----------------------------------------------------
Else (TAG, second-level judgment, otherwise, @ fTextCount = 0, indicating that the content table has no records)
--------------------------------------------------------
Begin (mark, level 2 Begin 2)
Delete From Navtion_NodeSubject Where fTopID = @ fTopID
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End (mark, second-level Begin second-End)
The above code is simple. If there is no record in the tText table (content table), delete the record in Navtion_NodeSubject of the secondary category table first, and then delete the record in Navtion_TopSubject of the primary category table.
------------------------------------
End (mark, second-level Begin ends)
-------------------------------------
Else (FLAG: Level 1 judgment otherwise, it indicates that the sub-category table has no content @ fNodeCount = 0)
-------------------------------------------------------------
Begin
Delete From Navtion_TopSubject Where fTopID = @ fTopID
End
The above Code directly deletes the record of Navtion_TopSubject in the main category table
-----------------------------------------------------
This trigger has ended, but we must prevent execution errors. Therefore, if an error occurs, roll back all operations and send an error message to the application.
Copy codeThe Code is as follows:
If @ Error = 0
Determine whether an error has occurred
@ Error is a global variable of SQL Server. If an Error occurs while saving the previous SQL command, @ Error = 1
If it is 0, there is no error.
-------------------------------------------
Commit Transaction
The above is a statement without errors, indicating that the transaction is committed (it can be considered as the end of the transaction)
If you have the code for starting a transaction with Tran in Tran, you must add this code later. Otherwise, the transaction will not end, and the database record will be locked and you will not be able to operate on the database record!
--------------------------------------------
Else
@ Error the variable is greater than 0, indicating an Error.
----------------------------------
Begin
Rollback Transaction my_Save1
Raiserror ('deletion error, record: % s and its content are not deleted. ', 16,1, @ fTopName)
End

The above code is a statement block, indicating what to do if the above deletion statement fails
Rollback Transaction my_Save1
Rollback Transaction is an internal command of SQL Server, indicating that the Transaction is rolled back, that is, all previous operations are canceled. My_Save1 is the name of the saved vertex.
This code means that the game is not successful, and the restoration from the inventory file is the same. : D
----------------------------------------------------------------------
Raiserror ('deletion error, record: % s and its content are not deleted. ', 16,1, @ fTopName)
Raiserror indicates a custom error message, which is used to send messages to SQL Server applications.
For example, asp code, the information obtained by asp code is displayed on the webpage.
16. 1 is the wrong grade number. I will not explain it too much. If you are interested, you can view
T-SQL language reference.
% S is the custom variable % s of the Reiserror command, which indicates the string variable. The content is followed by @ fTopName.
This statement is similar to the following VBScript code (Suppose VBscript has The Raiserror command)
Raiserror ('deletion error, record: '& @ fTopName &' and its content are not deleted. ', 16,1)
-------------------------------------------------------------
All right, I have explained all the code of this trigger in detail. I hope you can understand it and improve the database operation capability through the above explanation!