SQL server trigger simple learning

I used triggers to do things in a short time, and I reviewed them. I found some information from the Internet and added my memories as follows. I only recorded the information for subsequent queries.

Create a trigger:

Create trigger name
On Table Name
For action (insert | update | delete)
As
If update (recommend) -- Condition
Begin
Trigger statement
End

 

The instead of statement is used to replace the action to be executed, as shown in figure

Create trigger name
On Table Name
Instead of action (insert | update | delete)
As
Begin
Trigger statement
End

Only the custom statements in the trigger will be executed without executing the corresponding action.

Similarly, replace instead with "after" or "before" to indicate that the action is executed after and before the action, respectively.

The key lies in the Inserted Table

Two special tables are used in the trigger statement:DeletedTable andInsertedTable.
The Deleted table is used to store copies of rows affected by the DELETE and UPDATE statements. When executing the DELETE or UPDATE statement, the row is deleted from the trigger table and transmitted to the deleted table. The Deleted table and the trigger table do not have the same rows.

The Inserted Table is used to store copies of rows affected by the INSERT and UPDATE statements. In an insert or update transaction, the new row is added to both the inserted Table and the trigger table. The rows in the Inserted Table are copies of the new rows in the trigger table.

1. Insert)
The Inserted Table has data, and the Deleted table has no data.

2. Delete)
The Inserted Table has no data, and the Deleted table has data.

3. Update)
The Inserted Table has data (new data), and the Deleted table has data (old data)