Detailed explanation of triggers in SQL

Trigger

Introduction to triggers:

A trigger is a special stored procedure. Its execution is not called by a program or started manually, but is triggered by an event. When a table is operated (insert) , delete, update), it will be activated for execution. Triggers are often used to strengthen data integrity constraints and business rules. In my opinion, a trigger is actually an event, just like in C#, clicking a button will trigger the corresponding operation.

Classification of triggers:

(1) DML (Data Manipulation Language) trigger: means that the trigger will be enabled when a DML event occurs in the database. DML events refer to insert, update, and delete statements that modify data in tables or views.
            (2) DDL (Data Definition Language) trigger: means that it will be enabled when a DDL event occurs in the server or database. DDL events refer to the create, alter, and drop statements in the table or index.
(3) Login trigger: It is triggered when a user logs in to a SQL SERVER instance to establish a session

​ Among them, DML trigger is the most commonly used, and it is divided into the following two situations according to the way DML trigger is triggered:

​ (1 ) AFTER trigger: It performs trigger operations after executing INSERT, UPDATE, and DELETE statement operations. It is mainly used for processing or checking after recording changes. Once an error occurs, the Rollback Transaction statement can be used to roll back this fastener. , but the AFTER trigger cannot be defined on the view.
(2) INSTEAD OF trigger: It performs the operations defined by the trigger itself before executing the INSERT, UPDATE, and DELETE statement operations. The INSTEAD OF trigger can be defined on the view.

INSERTED and DELETED

In SQL SERVER 2008, the implementation of DML triggers uses two logical tables DELETED and INSERTED. These two tables are built in the memory of the database server, and we only have read-only permissions. The structure of the DELETED and INSERED tables is the same as the structure of the data table where the trigger is located. When the trigger is executed, they will be automatically deleted: the INSERED table is used to store your insert, update, and delete statements. , updated record. For example, if you insert a piece of data, the record will be inserted into the INSERTED table: The DELETED table is used to store the trigger table you created before operating the insert, update, and delete statements. There are three pieces of data in the table, then he also has three pieces of data. In other words, we can use these two temporary memory-resident tables to test the effects of certain data modifications and set the conditions for trigger operations. Advantages and Disadvantages:

            Triggers can implement cascading changes through related tables in the database, and can enforce more complex constraints than those defined with CHECK constraints. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use SELECT in another table to compare inserted or updated data, and perform other operations. Triggers can also take countermeasures based on the table status before and after data modification (multiple similar triggers in a table. INSERT, UPDATE, or DELETE) allows multiple different responses to be taken in response to the same modify statement.

At the same time, although triggers are powerful and can easily and reliably implement many complex functions, why should they be used with caution? Too many triggers will make it difficult to maintain the database and applications. At the same time, excessive reliance on triggers will inevitably affect the structure of the database and increase the complexity of maintenance procedures. Grammar

Create trigger

CREATE TRIGGER 触发器名称  
ON 表名  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ DELETE ] [ , ]   
   [UPDATE ] }  
AS   
  SQL 语句 [ ... n ]

DROP TRIGGER 触发器名 [ , ... n ]

ALTER TRIGGER 触发器名称  
ON 表名  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ DELETE ] [ , ]   
   [UPDATE ] }  
AS   
  SQL 语句 [ ... n ]

disable trigger trigDB on database --禁用触发器  
enable trigger trigDB on database --开启触发器

Rreee

Create on S table UPDATE trigger:

print '删除了触发器***'   
raiserror('数据一致性验证',16,1)  
rollback transaction

It is forbidden to delete the records of students with failing grades in the SC table:

Create trigger tri_Updates  
on s  
for update   
as   
print 'the table s was updated'

CREATE TRIGGER tri_del_grade  
  ON SC FOR DELETE  
  AS  
    IF EXISTS(SELECT * FROM DELETED      
          WHERE Grade < 60)  
      ROLLBACK