MS SQL Basics Tutorial: The principle of triggers

As we can see from the above introduction that triggers are powerful, how does MS SQL Server manage triggers to accomplish these tasks? Below we will make a more detailed introduction to its working principle and realization.

Each trigger has two special tables: Insert table and delete table. The tables are logical tables and are managed by the system, stored in memory, not stored in the database, and are not allowed to be modified directly by the user. The structure of the two tables always has the same table structure as the table that the trigger acts on. These two tables are dynamically resident in memory, and when the trigger is finished, the tables are also deleted. These tables mainly hold the original data values or new data values that are affected by the user action. In addition, the tables are read-only, that is, users cannot write to the two tables, but they can reference the data in the table. For example, you can view the information in the deleted table with the following statement:

SELECT * from deleted

The features of the two tables are described in detail below:

12.7.1 the ability to insert a table

For a table that defines an insert type trigger, once an insert has been performed on the table, a corresponding copy is stored in the Insert table for all rows inserted into the table. That is, the Insert table is used to store content inserted into the original table.

12.7.2 the ability to delete a table

For a table that defines a delete type trigger, once a delete operation is performed on the table, all deleted rows are stored in the deleted table. The purpose of this is that once a trigger encounters a statement that forces it to abort, the deleted rows can be recovered from the delete table.

It is important to emphasize that the update operation consists of two parts, that is, the updated content is removed first, and then the new value is inserted. So for a table that defines an update type trigger, when the report is updated, the old value is stored in the Delete table, and the new value is placed in the Insert table.

Because triggers are activated only when the defined operation is executed, that is, the trigger executes only when an insert, delete, and update operation is performed. Each SQL statement can only activate the trigger once, and there may be a single statement that affects multiple records. In this case, the value of the variable @ @rowcount, which stores the number of records affected by the execution of an SQL statement, can be used to calculate the aggregate value of the records affected by the trigger's SQL statement after execution. In general, you first test the value of the @ @rowcount with the IF statement to determine whether the subsequent statement executes.

See the full set of "MS SQL Basics Tutorials"