Using triggers for data integrity in SQL Server

1. Means to achieve data integrity

In SQL Server, there are two main ways to achieve data integrity on the servers: one is to define data integrity when tables are created, mainly to include entity integrity, domain integrity, and cascading referential integrity, and the means to create primary key constraints, unique key constraints, check constraints, Default value constraints and various cascading integrity constraints. The other is implemented by writing trigger statements to implement various constraints on data table operations by defining trigger conditions and writing trigger execution statements.

2, the concept of trigger

A trigger is a special kind of stored procedure. Typically used to enforce business rules and data integrity. Triggers are automatically executed by the system through event firings. This is mainly reflected in the fact that it automatically triggers execution when inserting, deleting, or modifying data in a specified table to maintain data integrity, check data validation, implement database administration tasks, and related functions.

3. How triggers work

SQL Server creates two dedicated temporary tables for each trigger: the inserted table and the deleted table. The structure of the two tables is the same as the structure of the table that fires the trigger. These two tables are read-only tables, and users cannot modify them, and they can only query the contents of the table in the program that created the trigger.

When an INSERT statement is executed, the inserted statement table holds all the rows that will be inserted into the table. When the DELETE statement is executed, the deleted table holds all rows to be removed from the table. When an UPDATE statement is executed, it is equivalent to performing a delete operation before performing an insert operation, so the old row is moved to the deleted table, and the new row is inserted into the inserted table.

These two tables are deleted as the trigger executes.

Triggers are divided into two triggering modes: INSTEAD of Trigger and after trigger.

Where the INSTEAD of trigger is an alternative trigger: means that if there is an SQL statement that triggers a trigger, the pre-defined statement in the trigger is executed without executing the SQL statement. After trigger is post-trigger: means that if a SQL statement triggers a trigger, it does not immediately execute the pre-defined statement in the trigger, but waits until all the specified operations in the SQL statement have been executed successfully before firing the trigger.

4. The difference between a trigger and a data table constraint

1) You can reference fields from other tables. Triggers can reference other tables and can contain complex SQL statements. When modifying a table, the other tables are modified by triggers according to the relevant business rules, and once a violation of the business rules is found, the data can be restored to the pre-modified state by rolling back the statement.

2) can be in time to compare data changes before and after the difference. Because of the presence of inserted and deleted temporary tables in triggers, the user can compare the data before and after the operation, thus making it more explicit about the changes in the data table before and after the update.

5. Creating SQL Trigger statements

The basic syntax format for creating triggers using the CREATE TRIGGER command is as follows.

CREATE TRIGGER trigger name on table name | View Name

For INSERT | UPDATE | DELETED

After | INSTEAD of

As

SQL statements

6. Using triggers to achieve data integrity instances

The existing sales management database "Marketing" has the following main data tables.

Order information (order number, sales work number, item code, customer number, quantity, total amount)

Product information (code, name, stock quantity, supplier code, status, price, cost price)

Sales person (work number, department number, name, gender, telephone address)

(1) Creating an instance of an INSERT trigger

In the salesperson table, create an INSERT trigger: Check_ department number, when a user inserts a new salesperson into the Salesperson table, the action cannot be completed if the salesperson's department number does not exist at all in the Department information table. The statements are as follows:

Create Trigger Check_ department number on sales person

For insert

As

DECLARE @bmh int

Select @bmh = Department number from inserted

If @bmh not in (select number from department information)

Begin RAISERROR (' no this department! ', 7, 1)

ROLLBACK TRANSACTION

End

After a trigger is established, the Insert function cannot be completed when the user enters an error salesperson's information (the employee's department number does not exist).

(2) Create a DELETE trigger instance

When a user deletes an order from the Order Information table, it indicates that the user has returned the item, which is no longer ordered. In this case, we should fill in the inventory in the "goods information" in time, with the delete trigger to complete the operation.

Create trigger add_ stock on order information

For delete

As

declare @sl int, @hpbm int

Select @sl = Quantity, @hpbm = Item encoded from deleted

Update item information Set stock = Inventory amount [email protected] where code [email protected]

(3) Create an UPDATE trigger

Continue to create an update trigger on the ' Salesperson ' table: Update_ name, when the name of the salesperson is changed, the name of the change can be reflected in the "training" table.

Create Trigger Check_ Name

On sales Staff

For update

As

If update (name)

Begin

DECLARE @xm char (8), @gh int

Select @xm = name, @gh = Work number from inserted

Update training set name [email protected] where work number [email protected]

End

Go

7. Concluding remarks

In summary, a trigger is code that automatically throws execution on the operation of the data. A trigger can accomplish a constraint that cannot be achieved. The main benefit of a trigger is that it can contain complex processing logic that uses SOL code. Whether it is for maintaining consistency between database tables. The triggers can help the user to maintain the relevant integrity of the data or enforce business rules. Mastering the techniques for developing triggers. Write a high-efficiency trigger. will make the design of the database concise and efficient.

Using triggers for data integrity in SQL Server