T-SQL (trigger) of the database family

What is a trigger

A trigger is a special stored procedure that can only be invoked by an event-driven invocation.

What's the trigger for?

The database automatically invokes the appropriate trigger when certain actions are made on the database, the database, the table, the view, and the related objects. Eliminates the need to perform additions or deletions or stored procedures to make the program more automated.

How does a trigger work?

3.1 How triggers are triggered
The timing of SQL Server activation by triggers can be divided into two triggering modes, post-trigger and substitution triggering
The modified statement that causes the trigger to execute if a constraint is violated, the trigger is not activated when the trigger is triggered, and the trigger is activated in place of the triggering method
(1) Post-trigger
Triggers are executed after a modification statement that causes the trigger to execute, and through various constraint checks, is called a post-trigger
Create this trigger using the after or for keyword
Post-trigger can only be created on the table, but not on the view
(2) Substitution trigger
When executing a modification statement that causes the trigger to execute, the execution of the modified statement is stopped, and only its trigger is executed, which is called the substitution trigger
Create this trigger using the instead of keyword
Alternative triggers can be created on a table, or can be created on a view
3.2 Temporary table when activating trigger
When each trigger is activated, the system automatically creates two temporary tables for it
Inserted table: Data that needs to be added or updated
Deleted table: Data that needs to be deleted or updated
The structure of the two tables is the same as the table structure of the activated trigger
The two tables are automatically deleted when the trigger execution is complete
3.3 Example one: Override Trigger

Create TriggerTri_dropcustomer onCustomersinstead of Delete as    Delete  fromorderswhereCustomerID=(          SelectCustomerID fromdeleted)Delete  fromCustomerswhereCustomerID=(          SelectCustomerID fromdeleted)Go

3.4 Example Two: post-trigger

Create TriggerTri_updateorderdate onOrdersafterUpdate as    Declare @oldOrderdate datetime    Declare @newOrderdate datetime    Select @oldOrderdate=OrderDate fromdeletedSelect @newOrderdate=OrderDate frominsertedif @oldOrderdate<>@newOrderdate    begin        Print 'The order date cannot be modified'        rollback Transaction    EndGo

T-SQL (trigger) of the database family