Shulou(Shulou.com)06/01 Report--

Triggers are divided into three types by type:

1. DML trigger, which is triggered when data changes

2. DDL trigger, which is triggered when database-level or instance-level objects are modified

3. Login trigger, which is triggered when the user logs in

The most common is DML trigger, and DML trigger can be divided into two categories: INSTEAD OF trigger and AFTER trigger (some books mention FOR trigger, which is actually AFTER trigger, but it is written differently).

Functionally, INSTEAD OF triggers are used to replace the actual data modification operation, while AFTER triggers are used to perform subsequent operations after the actual operation is completed. For example, for DELETE operations, if we only want to modify the state of the data to indicate that the data has been deleted instead of deleting the data from the table, then we can use INSTEAD OF triggers; if we expect to record when the deletion occurred in other tables after deleting the data, then we can use AFTER triggers.

From an execution point of view, INSTEAD OF triggers and AFTER triggers are in different execution periods. The trigger sequence in SQL Server is:

1. Trigger INSTEAD OF trigger

two。 Trigger DEFAULT constraint

3. Trigger primary key / unique / CHECK constraint

4. Trigger foreign key constraint

5. Trigger AFTER trigger

So if you expect the modification to be performed smoothly without triggering the constraint resulting in a rollback, you can use the INSTEAD OF trigger to implement (modify the data in the INSTEAD OF trigger to make the data meet the constraint).

Because INSTEAD OF triggers overwrite the changes that actually occur, there can be only one INSTEAD OF trigger per modification type (DELETE/INSERT/UPDATE) on each table; AFTER triggers have no similar restrictions and can create multiple AFTER triggers.

The question is, in what order do AFTER triggers execute when there are multiple AFTER triggers? SQL Server allows you to specify an AFTER trigger that fires first and last for each modification type (DELETE/INSERT/UPDATE), but does not control the firing sequence of the rest of the triggers.

Specify the AFTER trigger that executes first:

-- specifies the AFTER trigger EXEC sys.sp_settriggerorder@triggername='tr_TB1_INSERT',@order='First',@stmttype='INSERT' that is first triggered for the INSERT operation

After talking about the trigger sequence, and then talking about the number of triggers, the pretending to say is: DML trrigers have statement scope and only fire just once regardless of how many rows affected. The popular saying is that for a statement, no matter how many lines (0 or 1000 lines) the statement modifies, the trigger corresponding to the operation type will be triggered and triggered only once.

PS: the Fire only once mentioned above is only for the executed SQL statement and does not contain the SQL statement inside the trigger

There are two special types of triggers in SQL server: Nested triggers and Recursive triggers, which are explained by Demo:

Nested triggers: create triggers on TB1 and TB2. When TR_TB1_INSERT1 on TB1 is triggered, statement execution in TR_TB1_INSERT1 causes TR_TB2_INSERT1 on TB2 to be triggered

-- create triggers on TB1 and TB2. When TR_TB1_INSERT1 on TB1 is triggered by--, the statement execution in TR_TB1_INSERT1 causes TR_TB2_INSERT1 on TB2 to be triggered, which belongs to Nested trigger CREATE TRIGGER TR_TB1_INSERT1ON dbo.TB1AFTER INSERTASBEGININSERT INTO TB2 (C1) SELECT C1 FROM insertedENDGOCREATE TRIGGER TR_TB2_INSERT1ON dbo.TB2AFTER INSERTASBEGINSELECT 1END.

Recursive triggers can be divided into direct recursive (Directed Recursive) triggers and indirect recursive (Indirect Recursive) triggers.

Direct recursive (Directed Recursive) triggers:

When a trigger is created in TB1, when the TR_TB1_INSERT1 on TB1 is triggered, the statement execution in TR_TB1_INSERT1 causes the TR_TB1_INSERT1 on TB1 to be triggered again

-create a trigger on TB1. When TR_TB1_INSERT1 is triggered on TB1, the statement execution in-- TR_TB1_INSERT1 causes TR_TB1_INSERT1-- on TB1 to be triggered again, which is a Directed Recursive trigger. ALTER TRIGGER TR_TB1_INSERT1ON dbo.TB1AFTER INSERTASBEGIN-- limits the number of recursive layers to 10 layers of IF (@ @ NESTLEVEL)

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.