Shulou(Shulou.com)05/31 Report--

This article is about the types of triggers in SQL Server. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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

Trigger is a method that SQL server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program, nor started manually, but triggered by events. For example, when an operation is performed on a table (insert,delete, update), it is activated. Triggers are often used to strengthen data integrity constraints and business rules.

Triggers have the following effect: data can be forcibly checked or converted before being written to the data table. When an error occurs in the trigger, the result of the change is 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 the specific situation.

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

1. DML trigger

When the data in the table in the database changes, including any insert,update,delete operation, if we write a corresponding DML trigger to the table, the trigger executes automatically. The main role of DML triggers is to enforce industry rules, and to extend Sql Server constraints, default values, and so on. Because we know that constraints can only constrain data in the same table, while triggers can execute arbitrary Sql commands.

2. DDL trigger

It is a new trigger added by Sql Server2005, which is mainly used to audit and standardize the operation of tables, triggers, views and other structures in the database. For example, in modifying tables, modifying columns, adding tables, adding columns, and so on. It is executed when the database structure changes, and we mainly use it to record the modification process of the database and to restrict programmers' changes to the database, such as not allowing certain specified tables to be deleted.

3. Login trigger

The login trigger fires the stored procedure in response to the LOGIN event. This event is raised when a user session is established with the SQL Server instance. The login trigger fires after the authentication phase of the login is complete and before the user session is actually established. Therefore, all messages from within the trigger that typically reach the user, such as error messages and messages from PRINT statements, are delivered to the SQL Server error log. If authentication fails, the login trigger is not fired.

Thank you for reading! This is the end of this article on "what are the types of triggers in SQL Server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.