INSTEAD of and after triggers

INSTEAD of Triggers
After triggers (also called "for" triggers) are executed after an INSERT, update, or delect action is triggered. For example, an after trigger on an Employees table is activated after an UPDATE statement is executed on the Employee table. Therefore, after triggers are triggered only after a row has been inserted or multiple rows and all constraints have been processed and passed. INSTEAD of triggers and after triggers are inherently different because INSTEAD of triggers are fired instead of triggering actions. For the same example, if you have a INSTEAD of UPDATE trigger on the Emplyees table and an UPDATE statement on the table, the result is that the UPDATE statement does not change any row in the Employee table. Instead, the UPDATE statement is only intended to trigger the INSTEAD of UPDATE trigger, which may or may not alter the data in the Employees table.
So, how do you decide to place INSTEAD of triggers at the right time and place? There are several key factors in making decisions that are worth considering. After triggers are used when the action must change the data in the table before the post-condition is executed. For example, after triggers can be used to log records that make any changes to the data in a relatively separate audit table. Intead of triggers can do the same job. However, the INSTEAD of trigger is less efficient in this case, because the update action is only allowed to execute after the action that it occurs is accurately recorded in the audit table.
In general, after triggers are more efficient than INSTEAD of triggers as long as they do not affect the modification of the data. After triggers are also a good choice when calculating data or modifying the data as a whole or as a whole fallback. For example, there is a rule that a rollback of more than 30% of the product price in the Products table must be rolled back. After triggers can do this beautifully, comparing the prices of products that have been inserted into the deleted table, and then rolling back the transaction if necessary. These are ideal conditions for after triggers, but sometimes INSTEAD is better.
INSTEAD of triggers have a big feature-that is, it allows you to use multiple complex query operations on a table or view instead of a single query. The INSTEAD of triggers can work on tables and views at the same time, unlike after triggers that only work on tables. I am often asked how to solve this situation: there is a multi-table view, how to update the view. If the view contains key fields and some fields that contain basic tables, this is simply a simple update of the base table. However, when there is more than one base representation in a view, the logical update is more complex than the one UPDATE statement alone. So, how do you use alternative tools to solve this problem? One way to do this is to put a instead of trigger on the view. INSTEAD of triggers can be defined on one or more tables. INSTEAD of triggers can be turned off in the range modified in multiple base tables.
For example, if a view merges tables such as Customers, products, orders, and orderdteils into one view, and the view uses the program to display all the data on the screen. The update operation is allowed to replace this view if there is a view of this kind: it contains four tables in the Northwind database and is named Vwcustomersordersorderdetailsproducts, which looks like this (Figure 1) ：

Figure 1 View of the connection Customers and its Order Details

CREATE VIEW vwcustomersordersorderdetailsproducts
As
SELECT C.customerid,
C.companyname,
O.orderid,
O.orderdate,
Od. UnitPrice,
Od. Quantity,
Od. Discount,
P.productid,
P.productname
From Customers C
INNER JOIN Orders o on c.customerid = O.customerid
INNER JOIN [Order Details] od on o.orderid = od. OrderID
INNER JOIN products p on od. ProductID = P.productid
GO
The Vwcustomersordersorderdetailsproducts view is connected to four tables, and each table exposes a sampled field. One thing to keep in mind is that when you design a trigger that contains INSTEAD of UPDATE, it is useful to include the primary key field for each table in the SELECT statement. Even if these fields are not used in the application, they are used in the INSTEAD of triggers to locate the rows that will be modified, and then modify the base table accordingly. Suppose you intend to allow the view to be updated to filter the base table by non-keywords. The update code should be written in the INSTEAD of UPDATE trigger, allowing the trigger to update the Compnayname column in the Customers table, the OrderDate column in the Orders table, the UnitPrice and Qu of the Order Details table The antity column and the ProductName column in the Products table. In this case, using after triggers is not appropriate, and INSTEAD of triggers is a good choice, see Figure 2:figure 2 Update view with INSTEAD of triggers

CREATE TRIGGER Tr_vwcustomersordersorderdetailsproducts_io_u
On vwcustomersordersorderdetailsproducts
INSTEAD of UPDATE
As
-Update Customers
UPDATE Customers
SET CompanyName = I.companyname
From inserted I
INNER JOIN Customers c on i.customerid = C.customerid

-Update Orders
UPDATE Orders
SET OrderDate = i.orderdate
From inserted I
INNER JOIN Orders o on i.orderid = O.orderid

-Update Order Details
UPDATE [Order Details]
SET UnitPrice = I.unitprice,
Quantity = i.quantity
From inserted I
INNER JOIN [Order Details] od on i.orderid = od. OrderID and
I.productid = od. ProductID

-Update Products
UPDATE Products
SET ProductName = I.productname
From inserted I
INNER JOIN Products p on i.productid = P.productid
GO
Note that the INSTEAD of UPDATE trigger in Figure 2 contains four update statements. Each UPDATE statement is intended to be modified for non-critical fields in one of the base tables. The UPDATE statement contains the key fields in each table that correspond to the fields in the view. This allows the UPDATE statement to locate the corresponding column in the corresponding table and modify only those columns. The following UPDATE statement tests the INSTEAD of triggers: Update vwcustomersordersorderdetailsproducts
SET Quantity = 100,
UnitPrice = 20,
CompanyName = ' Fake Name ',
OrderDate = "' 11/23/2001",
ProductName = "Widget"
WHERE OrderID = 10265
and ProductID = 17
If you check the values in the corresponding table (either through the view or the table itself), it is clear that the values have been updated. Of course, making some changes to the instead of triggers can have different results. For example, there is no requirement to write a trigger to change four base tables, so one or more UPDATE statements in the trigger can be deleted. Assuming that the INSTEAD of trigger is merely to update the value of the Order Details table, this will only update the fields in the Order Details table, ignoring any modifications on other base tables. In this case, there will be no error in the customers,products or the Orders table and no change will occur. Of course, if some fields in these three tables change, an error will occur. As I will be discussing in this article, the UPDATE and columns_updated functions are an ideal way to detect which fields have changed.
Figure 2 also shows how to write a trigger to modify multiple rows of records. Notice how the UPDATE statement joins the inserted table and the individual base tables by keyword. This guarantees that the update is for all rows, which are modified by the original UPDATE statement in the view. You can also do this by looping through the rows of records that are inserted into the table. In any case, it is a good idea to avoid using cursors, especially when using triggers. SQL SERVER is designed to process data in a dataset, and cursors are designed to process one data row at a time. Using cursors in triggers can degrade the performance of your program, so it is best to use a more efficient alternative to methods or use a subquery as in Figure 2.
Another way to change the insert of UPDATE trigger is to make it fire in the insert and DELETE statements of the view. This means that in the right place, the trigger will implement the INSERT or DELETE function. It must be remembered, however, that delete may delete more than one record, and the key is how the trigger is written tailed bird. Therefore, it is important to check the requirements of the triggers and test them before implementing them. The insert of UPDATE trigger can be written in a view, so it can insert a new customer, order, detailed order, and product. This trigger can also be used to check whether the customer is new before inserting a new customer (the same is true for other records). There are many opportunities when using INSTEAD of triggers, but, of course, triggers are the essence of the solution to the corresponding requirements.
In general, when an UPDATE statement referencing a table attempts to assign a computed, identical, or timestamp column, an error occurs because the values of these columns must be determined by SQL Server. These columns must be included in the UPDATE statement in order to satisfy the requirement that the column cannot be empty. However, if the UPDATE statement references a view with the INSTEAD of trigger, the logic defined in the trigger can bypass the columns to avoid errors. For this purpose, the trigger must not attempt to update the values of the corresponding columns in the base table (leaving them away from the SET clause of the UPDATE statement). When a processed record comes from an inserted table, a computed, identical, or timestamp column can use a dummy value to satisfy a non-null value, at which point the INSTEAD of trigger ignores these values, and the correct values are set by SQL SERVER.

  Update separate columns  
INSTEAD of triggers are also commonly used to update computed columns in base tables. For example, suppose there is a view called Vwordersorderdetailsproducts: CREATE view vwordersorderdetailsproducts
as
     select    O.orderid,
        o.orderdate,
         od. UnitPrice * od. Quantity as ExtendedPrice,
        p.productid, 
         p.productname
    from Orders o
         INNER JOIN [Order Details] od on o.orderid = od. orderid 
        INNER JOIN products p on od. ProductID = P.productid
GO

This view reveals a computed column called ExtendedPrice, which cannot be updated directly because it cannot change itself into a separate column in the table. Although you can implement such a business rule, in this rule extendedprice through this view to modify, quantity column should not be modified, but UnitPrice can be modified (I know this rule is a bit strange, but I can endure this). You can write a instead of UPDATE trigger to enhance this business rule, as shown in the code below: CREATE TRIGGER Tr_vwordersorderdetailsproducts_io_u
On vwordersorderdetailsproducts
INSTEAD of UPDATE
As
UPDATE [Order Details]
SET UnitPrice = i.extendedprice/quantity
From inserted I
INNER JOIN [Order Details] od on i.orderid = od. OrderID and
I.productid = od. ProductID

GO

The code shows how to replace the update of a computed column with a logic in the instead OF trigger. Suppose a product is quantity to 100 in a particular order table and ExtendedPrice is updated to 200, then the new UnitPrice value becomes 2. In this case, when executing an UPDATE statement that modifies the ExtendedPrice column, the end result is that UnitPrice is assigned the quotient of ExtendedPrice divided by quantity. The following code can be used to test this scenario: update   vwordersorderdetailsproducts
set      ExtendedPrice =
where    OrderID = 10265
and      ProductID =

  Check for changes
has the update and columns_update features in instead of and after triggers, and these two features allow the trigger to determine which fields are changed by the statement of the trigger. For example, the following trigger prevents any modifications to the LastName field in the Employees table. Here, the update function is used to determine which modifications to the field can be performed. An error occurs if the change is exceeded (and is not allowed to be modified). Paiserr or functions and transactions are rolled back, and fallback will undo any changes you have made. The update feature can work in agter triggers and instead OF triggers, not externally. CREATE TRIGGER Tr_employees_u on Employees after UPDATE as
    IF Update (lastname)
  & nbsp BEGIN
        RAISERROR ("Cannot Change LastName", 1)
         ROLLBACK TRAN
        RETURN
     END
GO

The

Update function is to determine whether a single column has been modified by an INSERT or UPDATE statement. Update (column) is a standard method for detecting updates. But it becomes less efficient when it is needed to detect if multiple columns are affected by an INSERT or UPDATE statement. And this is precisely the colum_update function of a bright spot. The Column_update function returns a bitmask to determine whether a particular column has been modified. A bitmask is a bit in a column that is contained in a table that is modified in order to define these columns in a table schema. If a row is modified, the value of this bit is 1, otherwise 0. Unlike the normal method of reading bytes from right to left, the bitmask is read from left to right. For example, the following code prompts a trigger in the Order Details table to detect if the quantity and UnitPrice two fields have been modified. CREATE TRIGGER tr_orderdetails on [Order Details] After UPDATE
as
    IF (columns_updated () = a)
    BEGIN
        RAISERROR ("' cannot change both UnitPrice and Quantity at the 
                     same Time ", 1)
        ROLLBACK TRAN
    END
GO

If this field is modified, an error occurs and the transaction is rolled back. Taking the Order Details table, the column_updated feature returns five bytes representing the fields in the Order Details table. As long as the third and fourth fields are modified, this happens, and it detects whether the bits have been assigned a value of 1. When the third and fourth are opened, it is as follows: 00110. L because this bitmask represents 2 powers, the first bit represents 1, the second bit represents 2, the third bit represents 4, the fourth bit represents 8, and the fifth bit represents 16 (yes, this is the reverse order of the normal binary numbers), so only the value of the UnitPrice and Quantity fields being modified bitmask is 00110, This value is 12 (4+8). Note that this trigger will rollback the transaction only if the UnitPrice and Quantity fields have been modified. If other fields are modified, the bitmask will be different, so it is not equal to the integer 12. If the trigger is modified to prohibit modification to these two fields, it can be rewritten as follows, even if the other fields are forbidden: ALTER TRIGGER tr_orderdetails on [Order Details] After UPDATE
as
    IF (columns_updated () >=)
    BEGIN
         RAISERROR ("Cannot change both UnitPrice and quantity 
                    at the same time ' ", 1)
         ROLLBACK TRAN
    END
GO

Notice how the column_updated feature now detects the bitmask but is less than or equal to 12. If you modify the contact unitprice,quantity and discount columns, the bitmask becomes 00111, representing the integer 28 (4+8+16). When there are not more than 8 columns in a table, the function returns five bytes that contain the first eight columns, and the nineth to 16th is in the second byte, and so on. This feature is more useful in determining which columns are allowed to be updated than the update feature that updates only to the column.
as described earlier, the trigger can roll back a transaction under Huang-specific conditional rule conditions, and when a trigger containing a rollback executes in the SQL script, the entire processing is canceled. Therefore, all data that is modified by the triggered action is rolled back by the rollback transacion statement. Although a rollback does not prevent the execution of a SQL statement from being triggered, all statements that follow the rollback transacion statement will be executed. In particular, when a trigger resumes execution of a statement following a ROLLBACK statement, any modifications made after the rollback are not rolled back. This happens because some transactions are canceled when a ROLLBACK transaction is executed in the trigger. So when a new query statement is executed, a new transaction with the previous transaction begins again. Therefore, it is generally not recommended to place any statements after the ROLLBACK TRANSACTION statement.
like rollback does not automatically exit the trigger, it does not automatically generate an error. If a rollback is required and an error must be generated, the PAISERR or statement should be placed immediately before the exit trigger code, following the rollback.

Conclusion
Modifications made to the data in the same table will fire the same instead OF triggers, which will not be called recursively. Therefore, if there is a instead of trigger in the Emplyee table, this trigger is used to update the employee table, and this does not occur when the same instead of trigger is called. If this is allowed, the update should be banned. Another difference between INSTEAD of triggers and after triggers is that text,ntext and image columns can appear in triggers for tables that are updated and deleted. These binary columns appear in the Triggers for updating and deleting tables as Varcahar values, but this is not their original data type.
This has a useful stored procedure-sp_helptrigger system stored procedure-to detect triggers. He returns the type of trigger defined on the table, which is passed to the stored procedure. In this way, you can see which triggers are associated with a table, what actions trigger these triggers and whether the trigger is an after trigger or a instead of trigger.
In the last two columns, I have discussed several aspects of after triggers and instead OF triggers. Of course, there are many situations in which they are useful, and many of them have not been used. When a trigger has to query other tables, the trigger is inefficient. In these cases, the performance and triggering actions of the trigger are greatly compromised. Triggers are a great tool when used well, but it's important to ensure that you have a thorough test of your program before using them.

INSTEAD of and after triggers