Implementation based on SQL Server trigger technology

xiaoxiao2021-03-06  28

In the database management system, how to ensure that data integrity in the database is an important topic. Data integrity refers to consistency of data stored in the database. It is mainly reflected in the following aspects: Entity Integrity, Domain Integrity, Reference Integrity, and User Define Integrity. Currently, there are many ways to solve this problem. From the most basic data type, to a variety of form constraints, although data integrity is proposed, it is more simple to be more simple, and the complicated data integrity problem cannot be solved. As a advanced technology, trigger (TRIGGER) can easily resolve any issues that guarantee data integrity. First, the working principle trigger for the trigger 1 trigger in the SQL Server environment is a special type of stored procedure, which is closely linked to the table, inserting, deleting, and updating the table, such as the table. (Also known as the trigger table) There is a trigger of the corresponding type of operation, the trigger automatically triggers execution. The trigger is divided into the INSERT trigger, the DELETE trigger, and the UPDATE trigger 3 class. When inserting data into the trigger table, the INSERT trigger will trigger execution, and the new record will increase in the trigger table and the Inseted table; when the data in the trigger table is deleted, the DELETE trigger will trigger execution, deleted The record will be stored in the deleted table; when the data in the trigger table is updated, it is equivalent to inserting a new record and deleting an old record. At this time, the UPDATE trigger will trigger execution, and the original record in the table is stored in the deleta table. , The modified record is inserted into the inserted table. The INSERTED table and the deleted table are two logical tables, which are maintained by the system, and the user is not allowed to modify the two tables directly. They are stored in memory and are not placed in the database. The structure of the two tables is always the same as the structure of the triggers. After the trigger is completed, the two tables associated with the trigger will also be deleted. DELETED table is used to store a copy of the row affected by the Delete and Update statements in the SQL language. When executing the Delete or Update statement, the row is removed from the trigger table and transferred to the deleted table. DELETED tables and trigger tables typically have no different rows; Inserted Table is used to store a copy of the row affected by the INSERT and UPDATE statements in the SQL language. In an insert or update transaction, the new CCB is added to the INSERTED table and the trigger table. The line in the INSERTED table is a copy of the new row in the trigger table. 2 Trigger implementation steps in the textbook management system developed by the author, establish a textbook database JCSJK, including the textbooks, JCB and textbooks, and make the MXB, which needs to be inserted, deleted, and modified to MXB, dynamically modified The stock of the corresponding textbook in the JCB. The implementation step of the trigger is illustrated below. In order to reduce the space, the table structure is simplified. The following is done in the Microsoft SQL Server environment.

(1) Create a textbook table JCB, and define the primary key Create Table [DBO]. [JCB] ([Teaching Material Code] [Char] (10) Not NULL,  [Teaching Material Name] [Char] (30) Not Null, [Decimal] (18, 2) Not NULL,  [Publishing House] [CHAR] [25) NULL in ON [Primary]  Lalter Table [DBO]. JCB] WITH NOCHECK ADD CONSTRAINT [PK-JCB] PRIMARY Key Clustered ([Textbook Code]) ON [PRIMARY] (2) Build a textbook into and out of the table MXB, and define primary keys and external? Br> create table [dbo]. [MXB ], [Textbook Code] [25) Not Null, in [Date] [DateTime] NOT NULL, in [Textbook] [INT] NULL,  [Teaching Materials] [INT] NULL,  [Note] [CHAR] NULL in ON [Primary]  al al al al al n]]. Al c (教 教)          ([Date] in the [Date] PRIMARY] ALTER TABLE [DBO]. [MXB] Add constraint [fk-mxb-jcb] Foreign Key ([Textbook code]) References [DBO]. [JCB] ( [Teaching Material Code] in DELETE CASCADE ON UPDATE Cascade Figure 1 shows the relationship between two tables of JCB and MXB.

(3) Establish Insert trigger Create Trigger MXB-INSETASTAS in MXB. [MXB] for Insertas = = = inventory (Select Textbook - Textbook from From Insert) from INSERTED, INSERTED WHERE JCB. Textbook code = INSERTED. Textbook code (4) Established delete trigger Create Trigger MXB-de ON [DBO] on MXB. [Mxb] for deleteasupdate JCB SET stock = stock - (SELECT textbook into - textbook From Deleted) from JCB, DELETED = DELETED. Textbook code 〖HT = (5) Built Update Trigger Create Trigger MXB-UPDA ON [DBO] on MXB. [MXB] for Updateasbegin     Stock = stock - (Select Textbook - Textbook from From Deleted) from JCB, DELETEDWHERE JCB. Textbook Code = Deleted. Teaching Materials Update JCB SET Stock Quantity = Inventory (SELECT Textbook - Teaching Material from Insert inSrom , INSERTED, Where JCB. Textbook code = INSERTED. Teaching material code END passes the three-class trigger of the MXB table, and when the user is inserted, deleted, and modified, it will be based on the information of the textbooks in MXB, will be based on the MXB table. Modify the inventory of the corresponding textbook in the JCB. Since the Inserted table and the deleted table involved in the trigger are stored in memory, the trigger is executed faster. 3 Design trigger Consider an important question that needs to be considered when writing trigger code is that the statement that trigger the trigger can be a statement that affects a single line, or it can be a statement affecting multi-line. This is common in Update and Delete triggers because these statements often work more. And this is relatively rare in the Insert trigger, because the basic INSERT statement only adds a line. However, since the INSERT trigger can be excited by INSERT INTO (Table_Name) SELECT statement, inserting a number of rows can cause a single trigger call. The three-class triggers involved in the MXB discussed above are a statement that affects the individual. Therefore, it is necessary to consider the influence of multi-line statements, here is discussed to the MXB INSERT trigger. (1) The INSERT trigger that can handle multi-line MXB If you want to perform multi-line insertion, the triggers in the above example may not process it, because the UPDATE statement assignment expression on the right side of the expression can only be one value, and Can't be a list of values. Therefore, the role of the trigger is to obtain the value of any row in the INSERTED table, and add it to an existing inventory value of a specific textbook code value in the JCB table. If a textbook code value has appeared many times in the Inserted table, it may not be possible to obtain the expected result. In order to correctly update the JCB table, the trigger must adapt to the possibility of multi-line in the Inserted table. This can be implemented through the SUM function, which calculates the total of the teaching materials for each textbook code in the inserted table. The SUM function is stored in the related subqueries (SELECT statements in parentheses). The subquery returns a single value in the INSERTED table.

转载请注明原文地址:https://www.9cbs.com/read-39666.html

New Post(0)