[Distinct] practical application of SQL triggers in maintaining database integrity

From: http://www.cio360.net/h/1784/333770-10872.html

1. Trigger Overview

Trigger is an important tool in SQL Server database applications. It is a special type of Stored Procedure and widely used. Generally, a stored procedure is called directly by the stored procedure name, while a trigger is triggered by an event. A trigger is created based on a table and associated with one or more data modification operations (insert, update, or delete) as part of the table. Triggers are closely related to tables in the database. For example, when an insert, update, or delete operation is performed on a table, the trigger is automatically executed.

SQL Server includes two types of triggers: DML trigger and DDL trigger. The DDL trigger is a new feature of SQL Server 2005. It is called when a Data Definition Language (DDL) event occurs on the server or database; DML triggers are called when a data operation language (DML) event occurs in the database. DML events include insert, update, or delete statements that modify data in a specified table or view. DML triggers can query other tables and contain complex Transact-SQL statements. DML triggers are used to enforce business rules when data is modified, and to extend the integrity check logic of Microsoft SQL Server 2005 constraints, default values, and rules. The trigger described in this article mainly refers to the DML trigger.

DML triggers include the after trigger, instead of trigger, and CLR trigger. After an insert, update, or delete statement is executed, the after trigger is executed. This article uses the after trigger as an example to describe the application of the trigger in maintaining data integrity. Its creation syntax is as follows:

Create trigger trigger_name

On table_name

After {[insert] [,] [update] [,] [delete]}

As

SQL _statements

[Return]

In the trigger application, we usually use two special tables: inserted Table and deleted table. These are all local tables for the current trigger. The two tables have the same structure as the table where the trigger is located and are always stored in the cache. When the delete trigger is triggered, copies of the rows deleted from the affected table will be placed in the deleted table. Similarly, when an insert trigger is triggered, the inserted Table stores a copy of the newly inserted data row.

When one trigger is executed to stimulate another trigger, and the other trigger fires the third trigger, the nesting of the trigger occurs. That is, the nested trigger used below. DML triggers and DDL triggers can be nested at a maximum of 32 layers.

2. 1. trigger application

Taking the operations on multiple associated tables in the BBS Forum database as an example, this article describes the application of triggers in maintaining data integrity and consistency.

In the BBSProgramDuring the design, we often encounter operations on a data table, but also automatically operate on several other associated data tables to ensure data integrity and consistency between data tables. Commonly used data tables in BBS forums include:

Bbs_user table (storing user information): used to store user information. Fields include user name, password, points, number of posts, level ID, last post, QQ, email, profile picture, registration time, etc;

Bbs_type table: used to store large Forum information. Fields include Forum ID and Forum name;

Bbs_lanmu table: stores Forum information. The fields are divided into Forum ID, name, major forum ID, total topic count, reply count, and moderator;

Bbs_topic table: stores post information. The fields include the post ID, title, content, publisher, Sub-Forum ID, total replies, total clicks, last reply time, and reply;

Bbs_reply table: stores the reply information. The fields include the reply content, reply to, reply to the post ID, and reply time.

In the BBS Forum, triggers are mainly used in the following situations:

When a user posts a post in a sub-forum, the bbs_topic table is operated, but the total number of Forum topics in the sub-forum table bbs_lanmu is automatically increased by 1, you also need to update the bbs_user table to add corresponding points to the user. When the user points reach a certain score, the user's level ID is automatically updated, indicating that the user has been upgraded to a certain level.

When you reply to a post and operate on the bbs_reply table, you also need to add 1 to the total number of replies in the bbs_lanmu table in the sub-forum table, 1 to the total number of replies to the bbs_topic table, and update the last reply title and time in the table, in addition, the number of user posts in the bbs_user table is increased by 1, corresponding points are automatically added, and the last post title and time are updated;

In the background management of the Forum, administrators sometimes need to add or delete a large forum. When we want to delete a large section of the bbs_type table, to ensure data integrity and consistency in each table of the database, delete the associated data records in the bbs_lanmu table, bbs_topic table, and bbs_reply table.

The following describes how to apply a trigger when deleting a forum Forum in the future.

(1) Demand Analysis

There are many large sections in a forum, each of which corresponds to multiple sub-forums. Each forum corresponds to multiple posts, and each post corresponds to Multiple replies. Therefore, four associated tables are required to store the corresponding information: bbs_type table (storing large Forum information), bbs_lanmu table (storing Forum information), and bbs_topic table (storing post information) and bbs_reply table (store reply information ). Bbs_type has one-to-multiple relationships with bbs_lanmu, bbs_lanmu, bbs_topic, bbs_topic, and bbs_reply.

When we want to delete a large section of the bbs_type table, to ensure data integrity and consistency in each table of the database, delete the associated data records in the bbs_lanmu table, bbs_topic table, and bbs_reply table. Because there is a one-to-many relationship between the three pairs, if it is implemented in a program or stored procedure, it is obviously difficult and unreasonable. Based on the trigger role and the relationship between the four tables, the nested trigger is used to delete the trigger. Create an After trigger for the bbs_type table, bbs_lanmu table, and bbs_topic table, which are triggered by the delete event. Using nested triggers can automatically delete related records in multiple tables in the database, greatly simplifying the business logic. This ensures data integrity and consistency, and the rationality and convenience of program design.

(2) create a trigger

Based on the requirement analysis, an after trigger is created for the bbs_type table, bbs_lanmu table, and bbs_topic table, which are triggered by the delete event. The trigger established on these three tables is a nested trigger relationship, that is, the trigger on the bbs_lanmu table triggers the trigger on the bbs_lanmu table, and the trigger on the bbs_lanmu table triggers the trigger on the bbs_topic table. The database used in this article is Microsoft SQL Server 2005. To enable nested triggers, you must set "recursive triggers enabled" to true in "attributes" of the database.

1. Create a trigger deltype for the bbs_type table (storing information about large sections. This trigger function is used to delete all Forum information in the deleted large section of the bbs_lanmu table.

Create trigger [deltype] on [DBO]. [Bbs_type]

After Delete

As

Begin

Declare @ typeid int

Select @ typeid = typeid from deleted -- Obtain the ID of the forum to be deleted

Delete from DBO. bbs_lanmu where typeid = @ typeid

End
2. Create a trigger dellanmu for the bbs_lanmu table (storing Forum information. This trigger function is used to delete all posts in the bbs_topic table that have just been deleted from the Sub-Forum.

Create trigger [dellanmu] on [DBO]. [Bbs_lanmu]

After Delete

As

Begin

Declare @ lmid int

Select @ lmid = lmid from deleted -- Obtain the Sub-Forum ID to be deleted

Delete from DBO. bbs_topic where lmid = @ lmid

End

3. Create a trigger deltopic for the bbs_topic table (storing post information. This trigger function is used to delete all replies to the deleted post in the bbs_reply table.

Create trigger [deltopic] on [DBO]. [Bbs_topic]

After Delete

As

Begin

Declare @ TID int

Select @ tid = TID from deleted -- get the ID of the post to be deleted

Delete from DBO. bbs_reply where tid = @ tid

End

Execution Process

When the data operation layer issues a delete record for the bbs_type data table, the trigger deltype is triggered. This trigger deletes all the sub-Forum information of the deleted large section in the bbs_lanmu table.

When the deltype trigger deletes a record from the bbs_lanmu table, the trigger dellanmu on the bbs_lanmu table is triggered. This trigger deletes all posts in the bbs_topic table that have just deleted the Sub-Forum.

When the dellanmu trigger deletes a record for the bbs_topic in the data table, the trigger deltopic is triggered. This trigger deletes all the reply information in the bbs_reply table that just deleted the post.

At this point, all records associated with the deletion records in the database and bbs_type are deleted, ensuring the integrity and consistency of data in each table of the database. This process is automatically performed in the database, so the speed is very fast. You only need to issue a command to delete a record for the bbs_type table, and the relevant records in other tables will be automatically deleted.

3. Summary:

triggers maintain data integrity and consistency, it can easily modify a table and automatically update records of other related tables to ensure data integrity. Triggers play an important role in database applications. Whether it is a way to provide advanced reference Integrity functions, or to execute tasks that automatically maintain unstructured data, triggers can help users implement rules that meet actual needs and simplify business logic, and make the system more convenient and effective