Database: Triggers

The concept of a trigger:

is a special type of event-driven process that user-defined on a relational table. Once defined, any additions and deletions to the table are automatically activated by the server, and centralized integrity control is performed at the core of the DBMS. Similar to constraints, but more flexible than constraints.

Categories of triggers:

DML triggers: DML (data manipulation Language) triggers are stored procedures that are executed when a data manipulation language event occurs in the database server. DML triggers are divided into two categories: after triggers and instead OF triggers

DDL triggers: DDL triggers are stored procedures that are executed when responding to data definition language (Language) events. DDL triggers are typically used to perform administrative tasks in the database. such as audit and standardize database operations, prevent database table structure is modified, etc.

After triggers: This type of trigger is activated only after the record has been changed (after), it is primarily used to record the processing or inspection after the change, and once an error is found, the operation can be rolled back and forth with the ROLLBACK TRANSACTION statement.

Instead of triggers: This type of trigger is typically used to replace the original operation, which occurs before the change is recorded, and it does not perform the operation (Insert, Update, Delete) in the original SQL statement, but executes the action defined by the trigger itself.

In SQL Server, each DML trigger is assigned two special tables, one for the inserted table and one for the deleted table. These two exist in the memory of the database server, are logical tables managed by the system, are two temporary tables, not physical tables that are actually stored in the database. The user has only Read permissions on both tables and no modified permissions.

The structure of the two tables (primary foreign key, field, data type, etc.) is exactly the same as the structure of the data table where the trigger is located, and the two tables will be removed from memory when the trigger's work is complete.

To define a trigger:

Use the CREATE TRIGGER command to create a trigger with the general format:

CREATE Trigcer < trigger name >| before| after| < trigger event >ON< table name >for each | row| Statement| {When < trigger condition >}< trigger action body >

1. Trigger name: Can contain the schema name, can not contain, in the same mode, the trigger name must be unique, and the trigger name and < table name >

Must be in the same mode.

2. Table name: The table data is changed, the activation definition on the table corresponding < trigger event > trigger, also known as the target table of the trigger.

3. Trigger event: Can be insert, DELETE. UPDATE, or it can be a combination of these several events. Indicates which columns are modified when the trigger

combination, which can be a combination of several events and can be appended with the < trigger column >.

4. Trigger type: Row-level trigger (for each row), statement-level trigger (for each STATEMENT).

5. Trigger condition: When activated, the trigger action is executed only if the trigger condition is true, and if you omit when, the trigger body is in touch

Executes immediately after the generator is activated

Database: Triggers