Differences between an After trigger and an instead trigger

The After trigger (also called the "for" trigger) will be executed after the insert, update, or delect action is triggered. For example, an after trigger on an employees table will be activated after an update statement is executed on the employee table. Therefore, the after trigger is triggered only when one or more rows have been inserted and all constraints have been processed and passed. The instead of trigger is essentially different from the after trigger because the instead of trigger instead of the trigger action is triggered. For example, if there is an instead in the emplyees table
Of update trigger and execute an update statement on this table. The result is that this update statement does not change any row in the employee table. On the contrary, this update statement is only used to kick the instead of update trigger. This trigger may or may not change the data in the Employees table.

Therefore, how can we decide to place the instead of trigger at the right time and position? There are several key factors worth considering when making a decision. The After trigger is used only after the action changes the data in the table. For example, the after trigger can be used to record any changes to the data in a relatively independent audit table. The intead of trigger can also do the same work. However, the efficiency of the instead of trigger is relatively low in this case, because the update action can only be executed after the action is accurately recorded in the audit table.

Generally, as long as data modification is not affected, the after trigger is more efficient than the instead trigger. When the data is calculated, the data is submitted as a whole, or the data is rolled back as a whole, the after trigger is also a good choice. For example, if the product price in the products table changes by more than 30%, the product price must be rolled back. The After trigger can perform this job beautifully. It uses the inserted product prices in the same table as deleted for comparison, and then rolls back the transaction as necessary. These are ideal conditions for the after trigger, but sometimes instead of will be better.

the instead trigger has a major feature: it allows you to replace a single query with multiple complex query operations in a table or view. Only after triggers can be used. table different functions, instead
of triggers can work on both tables and views. I am often asked how to solve this problem: there is a multi-Table view and how to update it once. If a view contains a keyword segment and some fields in a basic table, this is just a simple update of the basic table. However, when a view contains multiple basic representations, logical update is more complex than a single update statement. Therefore, how do you use an alternative tool to solve this problem? One method is to place an instead of trigger on The View. The instead of trigger can be defined on one or more tables. The instead of trigger can be transferred to the scope of modification in multiple basic tables.