MS SQL Basics Tutorial: Triggers overview

In the previous sections we described the general meaning of stored procedures, that is, user-defined stored procedures and system stored procedures. This section describes a special stored procedure, that is, a trigger. In the rest of the sections, we will give a detailed introduction to the concept, role, and usage of triggers, giving readers an idea of how to define triggers, create and use a variety of different complexity triggers.

The concept and function of 12.5.1 trigger

A trigger is a special type of stored procedure that differs from the stored procedure we described earlier. Triggers are executed primarily by triggering events, and stored procedures can be invoked directly by stored procedure names. When you perform actions such as update, INSERT, and DELETE on a table, SQL Server automatically executes the SQL statements defined by the trigger, ensuring that the processing of the data must conform to the rules defined by those SQL statements.

The main function of a trigger is its ability to achieve complex referential integrity and data consistency that cannot be guaranteed by primary and foreign keys. In addition, triggers have many different functions:

(1) Strengthened constraint (enforce restriction)

Triggers can implement constraints that are more complex than check statements.

(2) Tracking changes auditing changes

Triggers can detect operations within a database, allowing unauthorized updates and changes to be specified in the database.

(3) Cascade operation (cascaded operation).

Triggers can detect operations within a database and automatically cascade the contents of an entire database. For example, a trigger on a table contains a data operation (such as delete, UPDATE, insert) on another table that causes triggers on the table to be triggered.

(4) A call to a stored procedure (Stored procedure invocation).

In response to database update contacts, the sender can invoke one or more stored procedures, even outside of the DBMS (database management system) itself through calls to external procedures.

This shows that triggers can solve some of the problems of high-level business rules or complex behavior restrictions and the implementation of custom records. For example, triggers can find differences in the state of a table before and after the data is modified, and perform certain processing according to the difference. In addition, multiple triggers for the same type of table (INSERT, UPDATE, DELETE) can take a variety of different processing for the same data operation.

Generally speaking, trigger performance is usually low. When a trigger is run, most of the time the system processes is spent referencing this processing of other tables, because the tables are neither in memory nor on the database device, and the delete table and the Insert table are always in memory. The location of the other tables referenced by the visible trigger determines how long the operation will take.

Types of 12.5.2 triggers

SQL Server 2000 supports two types of triggers: after triggers and instead OF triggers. The AFTER triggers are the triggers described earlier in SQL Server version 2000. This type of trigger requires that the trigger be triggered only after an action is executed (INSERT UPDATE DELETE) and can only be defined on the table. You can define multiple triggers for the same operation on a table. For after triggers, you can define which triggers are first triggered and which ones are last triggered, typically using system procedure sp_settriggerorder to accomplish this task.

INSTEAD of triggers represent actions that do not perform their defined operations (INSERT, UPDATE, DELETE), but only execute the trigger itself. You can define a instead of trigger on a table or a instead of trigger on a view, but you can define only one instead of trigger for the same operation.

See the full set of "MS SQL Basics Tutorials"