This is a more complex T-SQL code. Role: Cascade Delete, if you want to delete the record of the primary category table, delete the subsequent records and all articles contained in this category.
Main Class Table Name: NAVTION_TOPSUBJECT Primary Key FTOPID Char (36) Table Name: NAVTION_NODESUBJECT Foreign Key Same Name: Ttext Foreign Commitment The same code is as follows: // --------- Code start ----- ------------------------------------------ Create Trigger [RemovetopandNodeText] ON [ DBO]. [NAVTION_TOPSUBJECT] INSTEAD OF DELETE AS / * Defines the variable used by the trigger * / declare @ftopid char (36), @fnodecount int, @ftopName var, @ftopName varchar / * FTOPID key value to delete the required Assignment to @ftopid variable * / / * Start transaction * / begin TRAN REMOVE_TOPSUBJECT SET @ftopid = (select ftopid from deleted) set @ftopName = (select ftopname from deleted) / * Save before the save point, prevent errors * / save TRAN my_Save1 / * first determines whether there is relevant to the content subclass tables NodeSubject * / Set @fNodeCount = (Select Count (*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @fTopID) if @fNodeCount> 0 Begin / * is determined whether there is the content table tText The content * / set @ftextcount = (Select count (*) from ttext where ttext.ftopid = @ftopid) if @fTextCount> 0 Begin delete from ttext where ttext.ftopid = @ftop ID 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, record:% s and its contents are not deleted.
', 16, 1, @ ftopname) end // ------------------------------------------------------------------------------------------------------------------------------------------------------ --- Now I explain this trigger code in detail: First, the background is the article I wrote an article processing system, the classification level is two levels, that is, there are two-level directory Similar to this: News -> Domestic News - -> International News Tutorial -> ASP Tutorial -> C # Tutorial -> JSP Tutorial Download -> Tool Download -> Source Codes Download Each article is one of the above directories. In this way, there must be three tables in the database: the primary class table, the subordinate table, the article content table. Moreover, in order to ensure the integrity of the data, use the relationship (understanding SQL Server know), so that there is a subclass under the news class and have an article in each subclass, using the deltee statement to delete the class will have an error, because violating The data integrity is constrained, and the records belonging after the category will become a dead record. So the record of deleting the primary table must ensure that the subcategories and contents of this category are all deleted. The role of this trigger I wrote is to delete the trigger of the primary table category, and can delete the record of the primary table with the contents of the primary table category. Second, the database structure 1. Main class table table name: navtion_topsubject master: ftopid data type: CHAR Size: 36 Class: FtopName Data Type: VARCHAR Size: 30 2. Navtion_nodesubject Primary Key: FNodeID Data Type: CHAR Size: 36 Out: FTOPID Data Type: CHAR Size: 36 (Represents the main class of this record) Category Name: fnodeName Data Type: VARCHAR Size: 30 3. Content Table: Table Name: Ttext Primary Key: FID Data Type: CHAR Size: 36 Out of Foreign Commitment 1: FtopID points to the main class table represents the main class of the owner 2: fnodeId points to the secondary class, indicating the secondary category 3, the operation process 1. This trigger is placed in the primary category table Navtion_topsubject, The trigger condition is a delte statement. If you execute the delete command to this table, the run of this code will be triggered. 2. Define the trigger to accept the delivered DELETE statement and then execute the code according to this statement. 3. The standard SQL delete code is as follows: delete from navtion_topsubject where fyopid = 'aaa' (assuming the primary category to be deleted is news, the primary key number is AAA) 4. So we can use this AAA to find the secondary class and content table. Have something. 5. Using the deleted table, this table is a logical delete table, which is equivalent to the recycle bin in the Windows system. SQL Server system definition: If there is a trigger in a table, no operation is not directly performed, but a logical operation.
This operation is performed in the Inserted table (insert), Updated table (update), and the deleted table (delete). Take the deleted table, pass the DEL command in the SQL Server system. If there is a trigger, SQL first copies this record to the deleted table (this table is a temporary table, only to the trigger, trigger Automatically delete it after running the end), we can use this code: SELECT FTOPID from deleted to get the primary key content of the AAA in the table navtion_topsubject passed to SQL. 6. Put this primary key content into the variable, find a category table according to this variable, if there is no content, indicating that this record does not have any categories and articles, can be deleted directly. 7. If there is a content in the subclass table, then use this variable to find the contents of the AAA in the content table. If there is, delete, then delete the contents of the secondary class, if not, then directly delete the contents of the secondary class table. 8. When the record of the subclass table and the content table is completely removed, the contents of the primary class table is deleted. Fourth, the process definition looks for the quantity of subclass, assigns the value to the @NodeCount variable, and then determines if> 0 is assigned to @ftopid to find the TTEXT table to @ftextcount, if> 0 first delete the TTEXT table in line with @ftopid content records and then delete all content subcategory table Navtion_NodeSubject table in line with @fTopID all content removed last main category list Navtion_TopSubject otherwise in compliance with @fTopID first remove all content Navtion_NodeSubject sub-categories in line with @fTopID table and then delete the main category list Navtion_TopSubject in line with All contents of @ftopid Otherwise, only remove the main class table navtion_topsubject All content in line with @ftopid, code explanation Create Trigger [RemoveTopandNodeText] ON [DBO]. [Navtion_topsubject] Instead of delete as the code is the SQL statement of the trigger , Content: CREATE TRIGGER T-SQL keyword, indicating that a trigger [RemoveTopandNodeText] is the name of the trigger [DBO]. [Navtion_topsubject] indicates that the name of the table belonging to this trigger indicates that this trigger will pass the flip-to-shield. SQL command, turn to the command of the trigger (more words: In addition to the instead of keyword, there is the for keyword, indicating that the trigger is executed after executing the SQL statement passed, is generally used in INSERT and In Update) Delete: Indicates that the trigger condition of this trigger is the delete command, and if the operation is deleted, the code of this trigger will be executed.
AS indicates the following code is the trigger code: --------------------------------------- ---------------------------- / * Define the variables used by the trigger * / declare @ftopid char, @fnodecount int, @ftextcount Int The @ftopname varchar or more command is the variable used by the trigger. The declare command is equivalent to the DIM command of VBScript, but this statement can define multiple variables, without a variable, one command, and the variables are separated by English comma. @Ftopid is a variable name, SQLServer specifies that @ characters must be added in front of the variable name used in SQL Server. The variable is behind the variable type char is a fixed length string, and INT is an integer number type, and varchar is a variable length string. -------------------------------------------------- ------------------------ / * assign the FTOPID key value of the transfer you need to delete to @ftopid variable * / / * Start transaction * / begin TRAN REMOVE_TOPSUBJECT SET @ftopid = (select ftopid from deleted) / * Save before saving before, prevent error * / save TRAN MY_SAVE1 Code Explanation Explanation: Playing / * and * / between program comments, similar to < ! - and -> Begin TRAN RMEVE_TOPSUBJECT indicates starting transactions, where RemoveTopsubject is a transaction name. The transaction is a mechanism that guarantees the success of the operation. If it is wrong in the transaction, the transaction will roll back and will not affect the entire system. For example, if you define 3 operations A b C in your transaction, a record is inserted, b Delete a record, C updates a record. The program starts execution, if the A starts operation B, the B operation occurs after the execution, then the transaction starts rollback, and the inserted A will be canceled, returning to the state before the three operations do not perform. Set @ftopid = (select ftopid from deleted) This is the assignment command of SQL Server, saving the value of the variable @ftopid into the content of the ftopid field in the deleted table. Set @ftopName = (select ftopname from deleted) The same assignment command, assigns the name of the primary class you want to delete to the @ftopName variable, in the back of the wrong statement. Save TRAN MY_SAVE1 SAVE TRANs indicates that the transaction is saved. If an error occurs, you can save it with this save. Similar to the storage file inside the game. My_save1 is a saver name, which is equivalent to the repository file name.
Set @fnodecount = (Select Count (*) from navtion_nodesubject where navtion_nodesubject.ftopid = @ftopid) is also a variable assignment statement, setting the @fnodeCount variable saved the primary table record (@ftopid variable content) belonging The number of records in the category table navtion_nodesubject can be found in this post according to 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 (made a tag, first-level judgment) start judgment, if @fnodecount is greater than 0, indicating that the master wants to delete Category Records contain subcategories, not directly delete, must first delete subcategories. However, the delete subcategory must delete all the contents (articles) records contained under subcategories, so we must judge whether there is a content record ------------------------ ----------------------------------------- Begin (made a tag, first-class begin ) Represents multiple statements below IF, you don't understand, you can refer to no human faq posts ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------- set @ftextcount = (Select Count (*) from ttext where ttext.ftopid = @ FTOPID) The above code continues to assign a value, @ TextCount means all the contents (articles) records contained in the primary category records you want to delete ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Record, then delete the record of the subclass table, finally remove the record of the primary class table, so it will not be wrong.
Begin (Mark, Level 2 Beging) -------------------------------------------------------------------------------------------------------------------------------- ---------- Delete From tText Where tText.fTopID = @fTopID Delete From Navtion_NodeSubject Where fTopID = @fTopID Delete From Navtion_TopSubject Where fTopID = @fTopID end (numerals, the two end command Begin a) above The statement is simple, first delete the TTEXT table in the TTEXT table record (delete content table) and then delete the Navtion_NodeSubject Table Combine @ftopid record (delete subcatenal table) finally remove the Navtion_topsubject table Combine @ftopid record (Delete Main Class Table) -------------------------------------------------- - Else (Mark, Secondary judgment Otherwise, @ fTextcount = 0, referring to the content table is not recorded) ------------------------- ----------------------------- Begin (Mark, Level 2 Begin 2) delete from navtion_nodesubject where ftopid = @ftopid delete from navtion_topsubject Where ftopid = @ftopid end (tag, secondary begin two end) The above code is simple, the TTEXT table is not recorded, then the record in the secondary category table NAVTION_NODESUBJECT is first deleted, and then the record in the Navtion_topsubject of the primary class table. ------------------------------------ End (tag, secondary begin end) ----- -------------------------------- ELSE (Mark: First level judgment Otherwise, indicating that the secondary category is not content @ fnodecount = 0) ------------------------------------------------ ------------- Begin delete from navtion_topsubject where ftopid = @ftopid end The above code Directly delete the main class table NAVTION_TOPSUBJECT record --------------- ----------------------------------- This trigger has ended, but we must prevent an error. So if you go back, you have to roll all your operation and send an error message to your application.
