Database Object triggers can easily enter database series 6

I have a close understanding of the trigger. I saw a blog about the trigger of my senior brother. After reading it, I thought it was too difficult to get started. I finally got started with this plug-in! I would like to share with you this sharp Article, which is my personal supplement and mind map. Share: trigger Introduction: trigger is a special stored procedure, and its execution is not

I have a close understanding of the trigger. I saw a blog about the trigger of my senior brother. After reading it, I thought it was too difficult to get started. I finally got started with this plug-in! I would like to share with you this sharp Article, which is my personal supplement and mind map. Share: trigger Introduction: trigger is a special stored procedure, and its execution is not

I have a close understanding of the trigger. I saw a blog about the trigger of my senior brother. After reading it, I thought it was too difficult to get started. I finally got started with this plug-in! I would like to share with you this sharp Article, which is my personal supplement and mind map.

Share:

Introduction to triggers:

Trigger is a special stored procedure. Its execution is not called by a program or manual operation. It is triggered by an event. You should be familiar with the event, for example, Click events of buttons and Load events of webpages. The Click event of a button is triggered by clicking the button, and the trigger event is triggered by adding, deleting, modifying, and modifying a table. The trigger is activated when you add, Delete, or Update a database or table.

From SQL2005, triggers are divided into two categories based on different SQL statements: DML triggers and DLL triggers. DML triggers are classified into two types: After triggers and Instead triggers.

Trigger category:

DML trigger: DML (Data Manipulation Language) trigger is the stored procedure when a Data operation Language event occurs on the database server. DML triggers are divided into two types: After triggers and Instead triggers.

DDL trigger: a ddl trigger is a stored procedure in response to a Data Definition Language event. DDL triggers are generally used to execute management tasks in databases. For example, review and standardize database operations, and prevent the database table structure from being modified.

DML triggers:

Today we will mainly introduce DML triggers. DML triggers are divided into After triggers and Instead Of triggers.

After triggers: these triggers are activated and executed only after the record has been changed. They are mainly used to record the processing or check After the change. Once an error is found, you can also use the Rollback Transaction statement to roll back the operation.

Instead Of triggers: these triggers are generally used to replace the original operations. They occur before a change is recorded. They do not execute the operations (Insert, Update, Delete) in the original SQL statement ), instead, execute the operations defined by the trigger itself.

In SQL Server, each DML trigger is assigned two special tables: an Inserted Table and a Deleted table. They exist in the memory of the database server. They are logical tables managed by the system and temporary tables instead of physical tables actually stored in the database. You have only the read permission and no modification permission for the two tables.

The structure of the two tables (primary and Foreign keys, fields, and data types) is exactly the same as that of the data table where the trigger is located. After the trigger is completed, the two tables will also be deleted from the memory.

The roles of Inserted and Deleted tables are as follows:

Inserted: For insert record operations, the insert table stores the data to be Inserted. For update record operations, the insert table stores the records to be updated.

Deleted: for the update record operation, the delete table stores the updated record. For the delete record operation, the delete table stores the Deleted old record.

The above two statements may not be clear, so check whether the following table is clear?

Working principle:

How the After trigger works:

The After trigger is activated only After the SQL statement is executed. Take the delete record as an example: When SQL Server receives a delete SQL statement, the records to be Deleted by SQL Server are stored in the Deleted table and Deleted from the data table, activate the After trigger and execute the SQL statement in the After trigger. After the operation is completed, delete the Deleted table in the memory. The operation is complete.

In the product inventory table, if you want to delete a product record, the trigger can check whether the inventory quantity of the product is zero. If not, cancel the deletion operation. The database operations are as follows:

1. Receive the SQL statement and extract the product records that will be deleted from the product inventory table and place them in the delete table.

2. Delete the product record from the product inventory table.

3. Read the inventory Quantity Field of the product from the delete table to check whether it is zero. If it is zero, complete the operation and clear the delete table from the memory. If it is not zero, roll back and forth using the Rollback Transaction Statement (restoring the database and table to the state before deletion ).

How the Instead trigger works:

The Instead trigger is different from the After trigger. The After trigger is activated only After the Insert, Update, and Delete operations are completed. The Instead Of trigger is activated before these operations are performed, and the original SQL operations are no longer executed, instead, execute the SQL statement of the trigger instead of the original statement.

In the above example, When deleting a product record, replace the delete operation with Instead to query the inventory Of the product. The database operations are as follows:

1. Receive SQL statements, but not execute them. Instead, jump to the SQL statement following Instead

2. Extract the inventory of the product based on the input product ID to complete the operation.

Syntax:

REATE TRIGGER

ON <Table_Name>

AFTER | instead of <INSERT, DELETE, UPDATE>

AS

BEGIN

SQL statement

END

GO

Notes:

1. The After trigger can only be used for data tables and cannot be used for views. The Instead Of trigger can both be used. An Instead Of trigger cannot be created for a view With Check Option. Neither of the two triggers can be created on a temporary table.

2. A data table can have multiple triggers, but one trigger can only correspond to one table.

3. In the same data table, multiple After triggers can be created for each operation (such as Insert, Update, and Delete), while the Instead Of trigger can only create one for each operation.

4. If an After trigger is set for an operation and an Instead Of trigger is set, the Instead of trigger will be activated, and the After trigger will not be activated.

5. Different SQL statements can trigger the same trigger. For example, the Insert and Update statements can activate the same trigger.

6. The trigger name must be unique in the database. Because the trigger is created in a data table or view, many people think that as long as it is in different data tables, the trigger name can be the same. In fact, the trigger's full name (Server. database. owner. triggerName) must be unique, regardless of the data table or view in which the trigger is located.

7. AFTER keywords can be used For retrieval. They all mean the same and represent triggers that will be activated only AFTER the operations on the data table are completed correctly.

Personal Understanding:

Note: An after trigger occurs when an event occurs.AfterProceedCheck and confirm,

And instead of is in the eventBeforeProceedReplaceOperation.

DDL triggers are also divided into two types: database level and server level.

Attached a mind map to help digestion.