Application of SQL Trigger in maintaining database integrity

Triggers are an important tool in SQL Server database application and a special type of stored procedure, which is widely used. General stored procedures are called directly through stored procedure names, and triggers are executed through events. Triggers are created based on a table and associated with one or more data modification operations (inserts, updates, or deletes) and are visible as part of a table. Triggers are closely related to tables in the database, such as when an insert, update, or delete operation is performed on a table, and the trigger is executed automatically.

SQL Server includes two main types of triggers: DML triggers and DDL triggers. Where DDL triggers are new to SQL Server 2005, the triggers are invoked when data definition language (DDL) events occur in the server or database, and DML triggers are called when data Manipulation language (DML) events occur in the database. A DML event includes an INSERT, update, or DELETE statement that modifies data in a specified table or view. DML triggers can query other tables and can contain complex Transact-SQL statements. DML triggers are used to enforce business rules when data is modified, and to extend the integrity checking logic of Microsoft SQL Server 2005 constraints, defaults, and rules. The triggers described in this article mainly refer to DML triggers.

DML triggers consist of three types: after triggers, INSTEAD of triggers, CLR triggers. After triggers are executed after an INSERT, UPDATE, or DELETE statement operation is performed, the following triggers are used as an example to describe the application of triggers 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 application of triggers, we usually use two special tables: Inserted table and deleted table. They are all local tables for the current trigger. These two tables are identical to the structure of the table in which the trigger is located and are always stored in the cache. When the delete trigger is triggered, a copy of the rows removed from the affected table is placed in the deleted table. Similarly, when an insert trigger is triggered, a copy of the data row that was just inserted is saved in the inserted table.

A trigger nesting occurs when one trigger performs an action that fires another trigger, and another triggers the third. This is the nested trigger used in the following. DML triggers and DDL triggers can be nested up to 32 levels.

2.1. Application of Triggers

We take the operation of multiple relational tables in BBS forum database as an example to illustrate the application of triggers in maintaining data integrity and consistency.

In the BBS program design, we often encounter the operation of a data table, but also automatically to the other several related data tables to operate to ensure the data between the data integrity and consistency. BBS Forum in the commonly used data tables are:

Bbs_user table (store user information): Used to store user information. The field has user name, password, integral, post number, Grade ID, Last post, QQ, Email, Avatar, registration time, etc.

Bbs_type table: For storing large section information. The field has the section ID, the plate name and so on;

BBS_LANMU table: Storage of sub-forum information. Field has a sub-forum ID, name, belong to the big plate ID, the total number of topics, the total number of replies, moderators and so on;

Bbs_topic Table: Store post information. The field has the post ID, title, content, post person, belong to the Forum ID, reply total, click the total, the last time posted, replies to people, etc.

Bbs_reply Table: Store reply information. The field has the reply content, the reply person, the reply post ID, the reply time and so on.

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

When the user posts in the Forum, the Bbs_topic table to operate, but at the same time automatically to the Forum table BBS_LANMU inside the total number of forums added 1, but also to update the Bbs_user table to the user to increase the corresponding points, when the user points to reach a certain score, Automatically updates the user's level ID, indicating that the user has risen to a level.

When the user replies to the post, the Bbs_reply table operation, but also need to the Sub-forum table Bbs_lanmu in the total number of responses to increase 1, the total number of responses to the Bbs_topic table 1 and update the list of the final reply title and time, but also bbs_user the user's post number 1 , automatically increase the corresponding points, update the last post title and time, etc.;

In the background management of the forum, administrators sometimes need to add or delete a large section. When we want to delete a large section of the Bbs_type table, in order to ensure the integrity and consistency of the data in each table of the database, the data records associated with the BBS_LANMU table, Bbs_topic table and bbs_reply table are deleted as well.

The following will be in the background management of the forum to delete the large section of the application of triggers as an example to carry out the specific introduction.

(1) Demand analysis

In a forum there are many large sections, each of which corresponds to multiple forums. Each forum corresponds to multiple posts, each of which corresponds to multiple reply messages. Therefore, 4 related tables are required to store the corresponding information: Bbs_type table (Storage of large plate information), BBS_LANMU table (storage of sub-forum information), Bbs_topic table (store post information), Bbs_reply table (store reply information). Bbs_type and Bbs_lanmu, Bbs_lanmu and Bbs_topic, Bbs_topic and bbs_reply are a pair of many relationships.

When we want to delete a large section of the Bbs_type table, in order to ensure the integrity and consistency of the data in each table, it is necessary to delete the associated data records in the BBS_LANMU table, Bbs_topic table and bbs_reply table. Because there are 3 pairs of relationships, it is obviously difficult and unreasonable to implement in a program or stored procedure. Based on the role of the trigger and the relationship between the 4 tables, nested triggers are used to implement this deletion function. Establish an after trigger for the Bbs_type table, the Bbs_lanmu table, and the Bbs_topic table, which is triggered by the delete event. Nested triggers can automate the deletion of related records in multiple tables in a database, simplifying the business logic greatly. This is to ensure the integrity and consistency of the data, but also to ensure the rationality and convenience of program design.

(2) Creating triggers

According to the requirement analysis, an after trigger is established for the Bbs_type table, the Bbs_lanmu table, the Bbs_topic table, and the trigger is triggered by the delete event. The triggers that are built on top of these 3 tables are nested triggers, that is, triggers on the BBS_LANMU table are triggered on the Bbs_type table, and triggers on the Bbs_topic table are triggered on the BBS_LANMU table. The database used in this article is Microsoft SQL Server 2005. To allow triggers to nest fires you must set the recursive trigger enabled to true in the database properties.

1. Set up trigger deltype for Bbs_type table (store large section information). The trigger function is to delete all the sub-forum information in the BBS_LANMU table that belongs to the newly deleted large plate.

CREATE Trigger [Deltype] on [dbo]. [Bbs_type]

After delete

As

Begin

DECLARE @typeid int

Select @typeid =typeid from deleted--get the section ID to be deleted

Delete FROM dbo. BBS_LANMU where typeid= @typeid

End

2. Create trigger DELLANMU for BBS_LANMU table (storage sub-forum information). The trigger function is to delete all post information in the Bbs_topic table that belongs to the newly deleted sub-forum.

CREATE Trigger [DELLANMU] on [dbo]. [BBS_LANMU]

After DELETE

As

BEGIN

DECLARE @lmid int

Select @lmid =lmid from deleted--get the sub-forum ID to be deleted

Delete FROM dbo. Bbs_topic where lmid= @lmid

End

3. Create trigger deltopic for Bbs_topic table (storing post information). The trigger function is to delete all the reply messages in the Bbs_reply table that belong to the deleted post.

CREATE Trigger [deltopic] on [dbo]. [Bbs_topic]

After delete

As

BEGIN

DECLARE @tid int

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

Delete FROM dbo. bbs_reply where tid= @tid

End

Execution process

When the data manipulation layer emits a delete record to the datasheet bbs_type, the trigger Deltype is triggered and the trigger deletes all the sub-forum information in the BBS_LANMU table that is part of the newly deleted large section.

When the Deltype trigger deletes a record for the datasheet BBS_LANMU, it triggers the trigger DELLANMU on the BBS_LANMU table, which deletes all post information in the Bbs_topic table that is part of the forum that has just been deleted.

When the DELLANMU trigger deletes a record for the datasheet bbs_topic and triggers the trigger deltopic, the trigger deletes all the reply messages in the Bbs_reply table that are just deleted posts.

All records that are associated with deleting records in the Bbs_type database are removed to ensure the integrity and consistency of the data in the database tables. This process is done automatically in the database, so it's very fast, the user only needs to send a command to delete a record from the Bbs_type table, and the related records in other tables will be deleted automatically.

3.1. Summary:

Triggers maintain data integrity and consistency, and can easily update records of other related tables based on a table modification to ensure data integrity. In database applications, triggers play a very important role. Whether as a way to provide advanced referential integrity functionality or to automate the task of maintaining non-normalized data, triggers can help users achieve the rules that meet their actual needs, simplify business logic, and make the system more convenient and efficient.