What are the three types of triggers?

The types of triggers are: DML triggers, DDL triggers and login triggers. Trigger is a method provided by [SQL server] to programmers and data analysts to ensure data integrity; it is a special stored procedure related to table events; its execution is triggered by events.

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is related to table events. A special stored procedure whose execution is not called by a program or started manually, but is triggered by events. For example, when a table is operated (insert, delete, update), its execution is activated. Triggers are often used to enforce data integrity constraints and business rules.

Triggers have the following functions: They can force the data to be checked or converted before writing to the data table. When an error occurs in the trigger, the result of the transaction will be undone. Some database management systems can use triggers for data definition language (DDL), called DDL triggers. The abnormal instruction (INSTEAD OF) can be replaced according to specific situations.

SQL Server includes three general types of triggers: DML triggers, DDL triggers, and login triggers.

1. DML trigger

When the data in the table in the database changes, including insert, update, delete any operation, if we If the corresponding DML trigger is written in the table, the trigger will be executed automatically. The main function of DML triggers is to enforce business rules and extend Sql Server constraints, default values, etc. Because we know that constraints can only constrain data in the same table, while triggers can execute any Sql command.

2. DDL trigger

It is a new trigger in Sql Server2005. It is mainly used to audit and standardize the structures of tables, triggers, views and other structures in the database. operations on. For example, modifying tables, modifying columns, adding new tables, adding new columns, etc. It is executed when the database structure changes. We mainly use it to record the modification process of the database and to restrict programmers from modifying the database, such as not allowing deletion of certain specified tables.

3. Login trigger

The login trigger will fire the stored procedure in response to the LOGIN event. This event is raised when a user session is established with an instance of SQL Server. The login trigger will fire after the authentication phase of the login is complete and before the user session is actually established. Therefore, all messages from within triggers that would normally reach the user (such as error messages and messages from PRINT statements) are passed to the SQL Server error log. If authentication fails, the login trigger will not fire.

Recommended tutorial: "sql video tutorial"

The above is the detailed content of What are the three types of triggers?. For more information, please follow other related articles on the PHP Chinese website!