After and Instead of triggers, instead triggers

After and Instead of triggers, instead triggers

L 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.

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

How the After trigger works

 

The After trigger is activated and executed only After the record changes. Take the delete record as an example: When SQL Server receives an SQL statement to perform the delete operation, SQL Server first stores the records to be deleted in the delete table, and then deletes the records in the data table, activate the After trigger and execute the SQL statement in the After trigger. After the operation is completed, delete the table in the memory and exit the entire operation.

In the product inventory table, if you want to delete a product record, when deleting the record, the trigger can check whether the inventory quantity of the product is zero. If not, cancel the delete operation. Let's take a look at how the database operates:

(1) receive SQL statements, and retrieve the product records to 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 delete the table from the memory. If it is not zero, roll back and forth using the Rollback Transaction statement.

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, run the SQL statement of the trigger itself.

 

Note:

L The After trigger can only be used in the data table. The Instead Of trigger can be used in the data table and view, but neither Of the two triggers can be created in the temporary table.

L a data table can have multiple triggers, but one trigger can only correspond to one table.

L in the same data table, many After triggers can be created for each operation (such as Insert, Update, and Delete), but the Instead Of trigger only creates one for each operation.

L 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.

L although the Truncate Table statement is similar to a Delete statement, it cannot activate a Delete trigger. Because the Truncate Table statement is not logged.

L The WRITETEXT statement cannot trigger Insert and Update triggers.

L different SQL statements can trigger the same trigger. For example, the Insert and Update statements can activate the same trigger.

 

 

Use range Of the Instead trigger

The Instead Of trigger can be used in both data tables and views. We recommend that you use the Instead Of trigger in the following situations:

L the data in the database cannot be modified. For example, the call records of the Telecommunications Department cannot be modified. Once modified, the call count is incorrect. At this time, you can use the Instead Of trigger to skip the SQL statement Of the Update modification record.

L it is possible to roll back the modified SQL statement, such as example 2 in section 11.5.3. Using the After trigger is not the best method. If you use the Instead Of trigger, when you determine that the discount is greater than 0.6, the update operation is aborted to avoid rollback after data is modified, reducing the burden on the server.

L use a trigger in the View: Because the After trigger cannot be used in the view, if you want to use a trigger in the view, you can only use the Instead Of trigger.

L modify data in your own way: if you are not satisfied with the SQL statement, you can use the Instead Of trigger to control the data modification method and process.