Main Category table name: Navtion_topsubject primary Key Ftopid Char (36)
Sub-category Table name: Navtion_nodesubject FOREIGN Key ditto
Content Table name: Ttext FOREIGN Key ditto
Copy Code code as follows:
Start-------------Code--------------------
CREATE TRIGGER [Removetopandnodetext] on [dbo]. [Navtion_topsubject]
INSTEAD of DELETE
As
/* Define variables used by triggers/
DECLARE
@fTopID Char (36),
@fNodeCount Int,
@fTextCount Int,
@fTopName VarChar
/* Assign the transferred 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 points before deleting to prevent errors * *
Save Tran my_save1
/* First of all, to determine whether the subclass table Nodesubject has its own content * *
Set @fNodeCount = (Select Count (*) from navtion_nodesubject Where navtion_nodesubject.ftopid = @fTopID)
If @fNodeCount > 0
Begin
/* Determine whether the content table Ttext has its own 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 (' Delete error occurred, record:%s and its owning content has not been deleted. ', 16,1, @fTopName)
End
------------------End of Code---------------------
I. BACKGROUND
This is an article I wrote the processing system, classification level Two, that is, there are two levels of directory similar to this:
News
--> Domestic News
--> International News
Tutorial
-->asp Tutorial
-->c# Tutorial
-->jsp Tutorial
Download
--> Tools Download
--> Source code Download
Each article belongs to one of the above directories.
In this way, there must be three tables in the database: The main category table, the secondary category table, the article content table. And, in order to ensure the integrity of the data, use relationships (knowledge of SQL Server), so that if there are subclasses under the news class and there are articles under each subclass, using the Deltee statement to delete the class will cause an error, because the data integrity constraint is violated, and the record to which the category is deleted becomes a dead record. So deleting the primary table records must ensure that the subcategories and contents of this category are all deleted. The purpose of this trigger that I write is to delete the trigger for the main table category, and to delete the record of the primary table if there is content under the main Table category.
II. Structure of the database
1. Main Category Table
Table Name: Navtion_topsubject
Main health: Ftopid data type: Char size: 36
Category name: Ftopname data type: VarChar size: 30
2. sub-category Table
Table Name: Navtion_nodesubject
Primary key: Fnodeid data type: Char size: 36
FOREIGN key: Ftopid data type: Char Size: 36 (represents the main category to which this record belongs)
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 point to main category table representing main category
Foreign key 2:fnodeid points to the secondary category table, representing the secondary category to which it belongs
Third, the operation process
1. This trigger is placed in the main category table Navtion_topsubject, the trigger condition is the DELETE statement, and if a delete command is executed on the table, then the code is triggered to run.
2. Define the trigger to accept the passed DELETE statement, and then execute the code according to the statement.
3. Standard SQL deletion code is as follows:
Delete from navtion_topsubject Where fyopid = ' aaa ' (assuming the main category to be deleted is news and the primary key number is AAA)
4. This way we can use this AAA to find out whether the secondary category table and the content table have the content that they belong to.
5. Using the deleted table, this table is the logical deletion table, equivalent to the Recycle Bin inside the Windows system. SQL Server System Definition: If there is a trigger inside a table, then no action is made directly, but it is logically manipulated. This operation is performed in the inserted table (insert), updated table (update), deleted table (delete). Take the deleted table, pass to the SQL Server system in the DEL command, if there is a trigger, SQL first copy the record to be deleted to the deleted table (this table is a temporary table, only to the trigger, the trigger after the end of the run automatically deleted), We can use this code:
Select Ftopid from deleted
Gets the primary key content that is recorded as AAA in the table navtion_topsubject that is passed to SQL.
6. Put this primary key content into a variable, according to this variable to find the category table, if there is no content, indicating that this record does not have any secondary categories and articles, can be directly deleted.
7. If there is content in the secondary category table, then use this variable to find content in the table of contents, if there is, delete, delete the contents of the category table, if not, then directly delete the contents of the category table.
8. Delete the contents of the main category table when the records of the secondary Categories table and the content table are all deleted cleanly.
Iv. Process Definition
First find the number of subcategories, assign them to @nodecount variables, and then judge
If >0
Then according to @ftopid find Ttext table content assignment to @ftextcount, if >0
First delete all records that Ttext table conforms to @ftopid content
Then delete all content in the Navtion_nodesubject table that conforms to @ftopid in the subcategory table
Finally delete all content that conforms to @ftopid in the Master category table Navtion_topsubject
Otherwise
First delete all content that conforms to @ftopid in the Navtion_nodesubject subcategory table
Then delete all content that conforms to @ftopid in the Master category table Navtion_topsubject
Otherwise
Delete all content that conforms to @ftopid in the main category table Navtion_topsubject only
V. Code interpretation
CREATE TRIGGER [Removetopandnodetext] on [dbo]. [Navtion_topsubject]
INSTEAD of DELETE
As
The above code is the SQL statement that establishes the trigger, and the contents:
The CREATE TRIGGER T-SQL keyword, which represents a trigger
[Removetopandnodetext] is the name of the trigger
[dbo]. [Navtion_topsubject] Represents the name of the table to which this trigger belongs
INSTEAD of indicates that the trigger will pass in the SQL command passed in, and turn to the command to execute the trigger
(in addition to the instead of the keyword, there is a for keyword, indicating that this trigger executes the trigger content after executing the incoming SQL statement, typically in insert and update)
Delete: The trigger condition that represents this trigger is the Delete command, and if the table is deleted, the code for the trigger is executed.
As means that the following code is the code for the trigger:
-------------------------------------------------------------------
/* Define variables used by triggers/
DECLARE
@fTopID Char,
@fNodeCount Int,
@fTextCount Int,
@fTopName VarChar
The above command is defined as the variable used by the trigger, and the DECLARE command is equivalent to the Dim command in VBScript, but this statement can define a variable without a single command, and the variables are separated by commas in English.
@fTopID is the variable name, SQL Server stipulates that the variable name used in SQL Server must precede the @ character.
Variable type char is a fixed-length string, int is an integer numeric type, and varchar is a variable-length string.
--------------------------------------------------------------------------
/* Assign the transferred Ftopid key value to the @ftopid variable * *
/* Start Transaction * *
BEGIN TRAN Remove_topsubject
Set @fTopID = (Select ftopid from deleted)
/* Save points before deleting to prevent errors * *
Save Tran my_save1
The above code explains:
Between/* and * * is the program annotation, similar to the HTML inside the <!--and-->
Begin TRAN Remove_topsubject represents the start of a transaction, where Removetopsubject is the transaction name.
A transaction is a mechanism that guarantees a successful operation, and if there is an error in the transaction, the transaction rolls back without affecting the entire system.
For example, if there are 3 operations defined in the transaction a B C, is a insert a record, B deletes a record, C updates a record. The program starts executing, if the operation B,b after the success of a succeeds, then the transaction starts rolling back, the insert record a will be canceled and returned to the state before the 3 operations were performed.
Set @fTopID = (Select ftopid from deleted)
This is the assignment command for SQL Server, which saves the value of the variable @ftopid as the contents of the Ftopid field in the deleted table.
Set @fTopName = (Select ftopname from deleted)
The same assignment command assigns the name of the main class to be deleted to the @ftopname variable, which is used in subsequent error statements.
Save Tran my_save1
Save Tran indicates that the transaction is saved and, if an error occurs, can be recovered with this save. Similar to the disk file in the game. My_save1 is the save name, which is equivalent to the file filename.
On the next:
Set @fNodeCount = (Select Count (*) from navtion_nodesubject Where navtion_nodesubject.ftopid = @fTopID)
Also a variable assignment statement that sets the @fnodecount variable to save the number of records in the secondary category table Navtion_nodesubject to which the primary table record (@fTopID variable) is to be deleted
This is a reference to the statement that finds other tables that qualify according to the primary table record:
Http://www.dw-mx.com/forum/mb_forum/detail2.asp?f2_id=37&f3_id=9022&f3_name= Life with a smile
--------------------------------------------------------------------
If @fNodeCount > 0 (Mark, level judgment)
To begin with, if the @fnodecount is greater than 0, the primary category record that you want to delete contains subcategories that you cannot delete directly, and you must first delete the subcategories.
But deleting a subcategory must delete all the content (article) records that are contained under the subcategory, so we have to determine if there is a content record
-----------------------------------------------------------------
Begin (as a mark, first stage begin)
Represents an if multiple statements executed below, do not understand can refer to the Inhuman FAQ post
--------------------------------------------------------------
Set @fTextCount = (Select Count (*) from ttext Where ttext.ftopid = @fTopID)
The above code continues to assign, @TextCount the number of all content (articles) records that are included in the Master category record to be deleted
------------------------------------------
If @fTextCount > 0 (Mark, level two judgment)
To determine the number of records, greater than 0 indicates a record, you must first delete the records of the content table, delete the records of the subcategory table, and finally delete the records of the Master category table so that there is no error.
Begin (mark, level two beging one)
---------------------------------------------------
Delete from ttext Where ttext.ftopid = @fTopID
Delete from navtion_nodesubject Where ftopid = @fTopID
Delete from navtion_topsubject Where ftopid = @fTopID
End (mark, ending command for level two begin)
The above statement is very simple, first delete the Ttext table ftopid conform to @ftopid records (delete content table)
Then delete the Navtion_nodesubject table that conforms to the @ftopid record (delete the secondary category table)
Finally delete the Navtion_topsubject table that conforms to the @ftopid record (delete main category table)
----------------------------------------------------
Else (Mark, two-level judgment otherwise, represents @ftextcount=0, meaning the content table is not recorded)
--------------------------------------------------------
Begin (Mark, level two begin II)
Delete from navtion_nodesubject Where ftopid = @fTopID
Delete from navtion_topsubject Where ftopid = @fTopID
End (mark, level two begin two)
The above code is simple, Ttext table (content table) does not have records, then first deletes the record in the Navtion_nodesubject category table, then deletes the record in the main category table Navtion_topsubject.
------------------------------------
End (mark, level two begin)
-------------------------------------
Else (mark: first-level judgment otherwise, there is no content @fnodecount=0 the secondary category table)
-------------------------------------------------------------
Begin
Delete from navtion_topsubject Where ftopid = @fTopID
End
The above code deletes the main category table Navtion_topsubject Records directly
-----------------------------------------------------
The trigger was already over, but we had to prevent an error from executing. So if there is an error, roll back all the actions and send an error message to the application.
Copy Code code as follows:
If @ @Error = 0
To determine if an error occurred
@ @Error is a global variable in SQL Server, if there is an error saving the last SQL command, and if there is an error, @ @Error =1
If it is 0, it means no error.
-------------------------------------------
Commit Transaction
The above is a statement without errors that represents a transaction commit, (which can be considered an end transaction)
If you have a previous code beginning TRAN transaction, you must add this code, otherwise the transaction does not end, the database records will be locked, the database records can not operate!
--------------------------------------------
Else
@ @Error variable is greater than 0 to indicate an error
----------------------------------
Begin
Rollback Transaction my_save1
Raiserror (' Delete error occurred, record:%s and its owning content has not been deleted. ', 16,1, @fTopName)
End
The code above is a statement block that indicates what to do if the deletion statement above fails
Rollback Transaction my_save1
Rollback Transaction is an internal command for SQL Server that refers to rolling back the transaction, where all previous operations were canceled. My_save1 is the name of the saved point saved above.
This code meaning and play in the game did not pass, from the file to restore the same truth. :D
----------------------------------------------------------------------
Raiserror (' Delete error occurred, record:%s and its owning content has not been deleted. ', 16,1, @fTopName)
RAISERROR represents a custom error message that is used to send information to an application that uses SQL Server.
such as ASP code, the ASP code to obtain this information will be displayed on the Web page.
16,1 is the wrong grade number, this I do not explain too much, interested people can view SQL Server Books Online
T-SQL Language Reference.
%s is a custom variable%s for the Reiserror command that represents a string variable, and the content is the contents of the subsequent @ftopname.
This statement is similar to the following VBScript code (assuming that there is a RAISERROR command in VBScript)
Raiserror (' delete error, record: ' & @fTopName & ' and its owning content has not been deleted. ', 16, 1)
-------------------------------------------------------------
OK, this trigger all the code I explained in detail, I hope you can understand, and through the above explanation to improve the ability of database operation!
