Trigger Actions and Order
This section examines the two major type of triggers, which are AFTER triggers and INSTEAD OF triggers. Each of these triggers can be associated with INSERT , DELETE , and UPDATE actions. Because the actions for both triggers are similar, you will see most of the issues dealt with in the AFTER trigger section, and only exceptions listed in the INSTEAD OF trigger section. At the end of this discussion is an explanation of the order of triggers and how you can modify it. AFTER TriggersAFTER triggers are the most common type of triggers that you will find being implemented on systems today. This is primarily because they were the only type of trigger that existed prior to Windows 2000. Because they were the only trigger type, the FOR keyword was used in place of the AFTER keyword. In either case, these triggers fire after the data modification ( INSERT , DELETE , or UPDATE ) is carried out. The following sections look at each type of AFTER trigger that can be created. If you refer back to the CREATE TRIGGER statement, you will notice that rather than creating individual triggers, you can actually create one trigger that applies to all three actions using syntax like the following: USE Pubs GO CREATE TRIGGER ModifiedDataTrigger ON dbo.authors FOR INSERT, UPDATE, DELETE AS PRINT 'You changed something!' To make it easier to keep each action straight, look at each trigger action separately. INSERT TriggersINSERT triggers are used to modify and disallow data at the attempted change of a record. As you saw in a previous example, an INSERT trigger might be used on a sales table, approving insertions of only those records where the sales amount is greater than $50,000. INSERT triggers are not restricted to approving or rejecting inserts , but can also be used to perform additional actions during the data insert. Additional actions could include recording information about the insert (such as the time) in a log or alternate table. Complex business logic can be easily implemented through a trigger. When an INSERT is made to a database, constraints are applied to the data. Any INSERT statements that satisfy the constraints are accepted into the table. Before completing the statement, any INSERT triggers are fired sequentially. If you want to know what data is being inserted into the table, you can query the Inserted table. This table has the same data structure as the table that the INSERT statement applies to, and it can be used to find out what data was entered into the table. You can refer to this table only from within the trigger code. When you reference this table, its name is Inserted . NOTE Logging and Triggers You are not able to reference text, ntext, or image columns in an AFTER trigger because this data is not actually stored in the same data structures as the rest of the table. You can, however, reference this data when using an INSTEAD OF trigger. With non-logged operations, triggers do not fire or execute. Non-logged operations include bulk copy operations and TRUNCATE TABLE statements. The Inserted table might contain one or more rows. Your trigger code has to accommodate the difference between statements that apply to multiple rows and statements that apply to a single row. When possible, rowset commands rather than cursors should be used, because cursors might have a negative impact or performance. DELETE TriggersDELETE triggers control what happens after a DELETE is carried out. You can use this trigger to restrict what data is deleted from your database or to perform any other action you want when the data is deleted. A developer might want to restrict the deletion of data because of the data integrity problems that might be caused, or might want to cascade delete child records in other tables. Another use of the trigger might be to log the deletion, or copy deleted data to another table. For example, a developer might allow database users to delete customers who have been inactive for more than 300 days ( tested with a trigger), but might use the trigger to copy the customer data to a Past_Customers table. When a DELETE statement is executed against a table having a DELETE trigger, the record(s) being deleted are copied to the Deleted table. During the execution of the DELETE trigger, you can refer to the data in the Deleted table. Having this data available enables you to perform tasks such as archive the data or log what data was deleted. When all triggers have completed, the data in the Deleted table is flushed. During the execution of the trigger, you can reverse the deletion by issuing a ROLLBACK TRANSACTION statement, which rolls back to the beginning of the transaction that initiated the trigger. If you are not using transactions, then the statement that initiated the trigger is its own implicit transaction. UPDATE TriggersIn the same way that INSERT triggers are used to control what actions take place after an INSERT , UPDATE triggers can control what actions take place after an UPDATE . These actions can include restricting or allowing update modifications to be performed on one or more tables, logging the update, or any other action you can think of. Updating data means to modify existing data, which means that some data is lost or deleted and replaced with new data that is inserted. An example of when an UPDATE trigger might be used is when you are the DBA of a trading company, XYZ, and the company's policy is to take sales figures only during a certain period ”for instance, from May to June. These sales figures might need to be changed once in a while because of miscalculations. The DBA might want to implement a trigger on the Sales table that allows updates only when the UPDATE statement includes in the Date column any date value between May and June. UPDATE triggers are not always used to restrict data modifications, but can also perform other actions when the update occurs. For example, a developer might program an UPDATE trigger on a table so that each time someone changes that table, the change and the user are recorded in a special table and the RAISERROR statement is used to generate an alert. The developer might perform these actions because of the type of data that exists in the table. Similar to an INSERT statement, when an UPDATE statement is executed, SQL Server creates an identical copy of the data that is being inserted into the database. This data is found in the Inserted table. Because this inserted record is actually modifying an existing record, you technically end up with data being deleted from the database as well. The original record, prior to the update, can be found in the Deleted table. By using the Inserted and Deleted tables, SQL Server 2000 is able to retain what the data looked like before the update and how it looks after the update. In your trigger code, then, you can perform actions on either or both of these tables. One possible action is to take a copy of the original data ( Deleted table) and store it in another table ( Old_Records) so that the data can be referred to if an error is discovered at a later date. The Inserted and Deleted tables are both special tables stored in memory that are automatically created and managed by SQL Server. In addition to these three types of AFTER triggers, you can also work with INSTEAD OF triggers. INSTEAD OF TriggersBefore SQL Server 2000, the only type of trigger available was an AFTER trigger ”a trigger that contains a set of statements that fire after a modification to a table, after any applicable constraints have been applied. INSTEAD OF triggers are new to SQL Server 2000 and execute instead of the triggering action. In other words, INSTEAD OF triggers can change any data-modification statement into a customized action. INSTEAD OF triggers place all the rows of a DELETE statement that would have taken place into the Deleted table, and all the rows that would have been inserted into the Inserted table. You can then script an appropriate set of steps to validate the proposed action, before the application or violation of any constraints that might be implemented. Constraint violations roll back your last statement, whereas the INSTEAD OF trigger can test for violations and then modify the proposed action to avoid the constraint violation. If an INSTEAD OF trigger has already been executed, and the constraints are still violated, then any actions taken in the INSTEAD OF trigger are rolled back. As an example, imagine that you have an Employees table that contains a DeptID for the employee, and the DeptID is referenced in the Department table. When using constraints, you find that when people attempt to add employees with invalid department codes, the statement fails the constraints on the tables, and the statement is not committed or cancelled. This cancel occurs before the AFTER trigger fires, so you do not know who attempted the update. With the INSTEAD OF INSERT trigger, the proposed update (including who attempted it) can be logged, and you can issue a rollback before the constraints are applied. You might also decide that all invalid DeptID s should be replaced with a generic DeptID , and the insert should be allowed to complete. This can be accomplished in the INSTEAD OF trigger by re-issuing the INSERT statement from within the INSTEAD OF trigger. In this example, the INSTEAD OF INSERT trigger now tests for invalid DeptID s. If they are found, it replaces the DeptID with a generic DeptID , and then re-issues the INSERT statement. Because the INSERT statement is re-issued, you might think that the INSTEAD OF INSERT trigger fires again, but it does not. This trigger cannot cause itself to be called again. After the correct INSERT statement is executed, the INSTEAD OF trigger is skipped , and the rest of the statement is carried out. Just to stress this point because it is important, INSTEAD OF triggers are not fired recursively. If the INSTEAD OF INSERT trigger fires, and then proceeds to issue the INSERT on the table, the INSTEAD OF trigger would be skipped, constraints would be checked, and the AFTER trigger would fire. Failure at the constraints would cause the statement to be cancelled, and a rollback could be issued if the AFTER trigger fails. A rollback would reverse all actions performed since the original statement was issued, so any actions performed by the INSTEAD OF trigger would also be rolled back. One of the great benefits of INSTEAD OF triggers is that they can be added to views. The INSTEAD OF trigger is the only type of trigger that can be placed on a view. Normally, views that reference multiple tables are not updateable because changes can be made to only one table at a time. With the help of an INSTEAD OF trigger, these views can be made to appear to update multiple tables at once. Also, views based on multiple tables using joins cannot normally have data deleted, but with an INSTEAD OF trigger, this too can be accomplished. An INSTEAD OF trigger has access to the deleted table, so it can use this deleted information to find which underlying base table needs to have data deleted. The following are some guidelines that should be observed when INSTEAD OF triggers are created:
Trigger OrderOnly a few options allow you to change when a trigger is executed. One is when the trigger is created, and the other is with the sp_settriggerorder procedure. Recursion can also play a role in how triggers are fired or executed. The first thing to look at is the different types of triggers. Trigger Type OrderWhen you create a trigger, you create an INSTEAD OF , AFTER , or FOR trigger. Depending on the type of trigger, your trigger will execute at a different time. This is an issue that has been previously discussed in this chapter, but it is important, and it's worth covering again. When creating a trigger, you use one of the following statements:
In addition to the type of trigger you are working with, you can set a first and last trigger when you are working with AFTER triggers. First and LastBecause there can be only one INSTEAD OF trigger of each type on a table, there is no need or reason to attempt to set the fire order of INSTEAD OF triggers. However, there can be many AFTER triggers of each type set on a single table. You might have multiple triggers so that you can define multiple actions to take when data in a table is modified. Rather than having multiple triggers, you can alter a trigger, making it larger and larger to accommodate all the logic. In many cases, smaller, more specific triggers can be easier to manage, code, and troubleshoot. Because there are multiple triggers of each type, you might have a reason to want one to fire before the others, and therefore want to set the order of triggers. To change the fire order of triggers, you can use the sp_settriggerorder stored procedure. The syntax of the command is sp_settriggerorder ' triggername ', ' value ', ' statement_type ' where
Outside of FIRST and LAST , you have no control over how triggers will fire. If you have already specified a FIRST or LAST trigger, you cannot specify another trigger as FIRST or LAST . If you want to remove a FIRST or LAST option, the ALTER TRIGGER statement always removes this setting when it is executed. If you alter a trigger and want to leave it as FIRST or LAST , then you will have to re-execute the sp_settriggerorder statement. The sp_settriggerorder statement requires a statement_type value such as INSERT , UPDATE , or DELETE . If you have a trigger that executes for all these actions, and you want it to be the FIRST trigger, you have to execute sp_settriggerorder three times: once for each statement type. Recursive TriggersTriggers are used to keep the flow of data within the boundaries of proper business logic, and this is why they are so widely used. Their capability is equal to stored procedures, and they are able to change records in other tables. There are two types of recursion, and each type of recursion has a solution within the SQL Server. The two types of recursion are direct and indirect . Before any recursion takes place, the database option of recursive triggers must be set to ON . This option can be set with sp_dboption and defaults to OFF . With direct recursion, an update causes a trigger to execute, which attempts to perform an update to the original table, causing the trigger to execute again, and updating the table again. If not checked, this would continue an infinite number of times, but the default setting for the database disables recursive triggers. For example, take the following trigger created on the Northwind.dbo.Products table: CREATE TRIGGER update_stock ON Products FOR insert, update AS DECLARE @counter int SET @counter = @@ROWCOUNT DECLARE @ProductID int DECLARE @InStock smallint DECLARE @ReOrder smallint DECLARE @OnOrder smallint DECLARE @ReOrderQty smallint DECLARE stock_update_cursor CURSOR FOR SELECT ProductID, UnitsInStock, ReorderLevel, UnitsOnOrder FROM inserted IF @counter = 1 BEGIN SELECT @InStock = UnitsInStock, @ReOrder = ReorderLevel, @OnOrder = UnitsOnOrder FROM inserted SET @ReOrderQty = 5 IF (@InStock + @OnOrder) < @ReOrder BEGIN UPDATE Products SET UnitsOnOrder = @OnOrder + @ReOrderQty WHERE ProductID = (SELECT ProductID FROM inserted) PRINT 'Order for ' + RTRIM(@ReOrderQTY) + ' units has been placed.' END END ELSE BEGIN OPEN stock_update_cursor SET @ReOrderQty = 5 FETCH NEXT FROM stock_update_cursor INTO @ProductID, @InStock, @ReOrder, @OnOrder WHILE @@FETCH_STATUS = 0 BEGIN IF (@InStock + @OnOrder) < @ReOrder BEGIN UPDATE Products SET UnitsOnOrder = @OnOrder + @ReOrderQty WHERE ProductID = @ProductID PRINT 'Order for ' + RTRIM(@ReOrderQTY) + ' units has been placed.' END FETCH NEXT FROM stock_update_cursor INTO @ProductID, @InStock, @ReOrder, @OnOrder END CLOSE stock_update_cursor END DEALLOCATE stock_update_cursor When there is an insert or update to the table, the trigger fires and places an order of five units of the product if the UnitsInStock + UnitsOnOrder are less than the ReOrderLevel . This order actually updates the same record in the Products table. With the default database options, an insert like the one in Figure 8.6 would only result in one firing of the trigger, which is evident because only one order was processed and the UnitsOnOrder are set to only five units. Figure 8.6. Direct recursive triggers fire only once if recursive triggers are turned off. If you enable recursive triggers for your database, this trigger fires multiple times ”up to 32 times. You can use either of the following commands to enable recursive triggers : sp_dboption 'Northwind', 'recursive triggers', TRUE ALTER DATABASE Northwind SET RECURSIVE_TRIGGERS ON When you are using the recursive triggers option, the same insert will have very different results. In this case the trigger fires as many times as necessary to bring the UnitsOnOrder to a level that Reorders are not required. This is done in steps of five units, and can be seen in Figure 8.7. Figure 8.7. Direct recursive triggers might fire up to 32 times. With indirect recursion, an update causes a trigger to execute, which attempts to perform an update to another table, and the other table has a trigger that attempts to update the original table, which completes a circle of triggers. This circle of triggers would continue infinitely, unless checked. For an example of indirect recursion, add a new table to the Northwind database called ProductReplenishment . This table will actually hold the ReOrder orders. When a sale updates the Products table, the Update_Stock trigger will fire and possibly create a new ProductReplenishment order. The New_Order trigger on the ProductReplenishment table then completes the order by updating the Products table, thus firing the Update_Stock trigger again. This type of recursion occurs even if recursive triggers is set to false. The recursive triggers option only applies to direct recursion. The following script creates the new table and the triggers. Review it, and see the results in Figure 8.8. (Some of the results were deleted for readability.) CREATE TABLE ProductReplenishment (OrderID smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, ProductName nvarchar(40), SupplierID int, Quantity smallint) GO CREATE TRIGGER new_order ON ProductReplenishment FOR insert AS DECLARE @counter int SET @counter = @@ROWCOUNT DECLARE @ProductName nvarchar(40) DECLARE @Quantity smallint DECLARE @InStock smallint DECLARE @ReOrder smallint DECLARE @OnOrder smallint DECLARE @ReOrderQty smallint DECLARE Prod_Replenish_Cursor CURSOR FOR SELECT ProductName, Quantity FROM inserted IF @counter = 1 BEGIN SELECT @InStock = UnitsInStock, @ReOrder = ReorderLevel, @OnOrder = UnitsOnOrder FROM Products WHERE ProductName = (SELECT ProductName FROM inserted) SET @ReOrderQty = (SELECT Quantity FROM inserted) IF (@InStock + @OnOrder) < @ReOrder BEGIN UPDATE Products SET UnitsOnOrder = @OnOrder + @ReOrderQty WHERE ProductName = (SELECT ProductName FROM inserted) PRINT 'Order for ' + RTRIM(@ReOrderQTY) + ' units has been placed.' END END ELSE BEGIN OPEN Prod_Replenish_Cursor FETCH NEXT FROM Prod_Replenish_Cursor INTO @ProductName, @Quantity WHILE @@FETCH_STATUS = 0 BEGIN SELECT @InStock = UnitsInStock, @ReOrder = ReorderLevel, @OnOrder = UnitsOnOrder FROM Products WHERE ProductName = @ProductName SET @ReOrderQty = @Quantity IF (@InStock + @OnOrder) < @ReOrder BEGIN UPDATE Products SET UnitsOnOrder = @OnOrder + @ReOrderQty WHERE ProductName = @ProductName PRINT 'Order for ' + RTRIM(@ReOrderQTY) + ' units has been placed.' END FETCH NEXT FROM Prod_Replenish_Cursor INTO @ProductName, @Quantity END CLOSE Prod_Replenish_Cursor END DEALLOCATE Prod_Replenish_Cursor GO CREATE TRIGGER update_stock ON Products FOR insert,update AS DECLARE @counter int SET @counter = @@ROWCOUNT DECLARE @InStock smallint DECLARE @ReOrder smallint DECLARE @OnOrder smallint DECLARE @ReOrderQty smallint DECLARE Prod_Update_Cursor CURSOR FOR SELECT UnitsInStock, ReorderLevel, UnitsOnOrder FROM inserted IF @counter = 1 BEGIN SELECT @InStock = UnitsInStock, @ReOrder = ReorderLevel @OnOrder = UnitsOnOrder FROM inserted SET @ReOrderQty = 5 IF (@InStock + @OnOrder) < @ReOrder BEGIN INSERT INTO ProductReplenishment (ProductName, SupplierID, Quantity) VALUES ('Widget', 18, @ReOrderQty) PRINT 'Updated Products Table.' END ELSE BEGIN OPEN Prod_Update_Cursor FETCH NEXT FROM Prod_Update_Cursor INTO @InStock, @ReOrder, @OnOrder WHILE @@FETCH_STATUS = 0 BEGIN SET @ReOrderQty = 5 IF (@InStock + @OnOrder) < @ReOrder BEGIN INSERT INTO ProductReplenishment (ProductName, SupplierID, Quantity) VALUES ('Widget', 18, @ReOrderQty) END FETCH NEXT FROM Prod_Update_Cursor INTO @InStock, @ReOrder, @OnOrder END PRINT 'Updated Products Table.' END CLOSE Prod_Update_Cursor END DEALLOCATE Prod_Update_Cursor GO ALTER DATABASE Northwind SET RECURSIVE_TRIGGERS OFF GO Figure 8.8. Indirect recursion is not affected by the recursive trigger option. Notice that recursive triggers were disabled for the previous example, but the triggers still functioned with the required recursion. That was because of the indirect recursion. If you want to disable this default feature of SQL Server 2000, then you have to change the server configuration option for 'nested triggers' . If the nested triggers option is set to '0' , then trigger nesting (a trigger that causes another trigger to fire) is disabled. A nested triggers setting of '1' enables trigger nesting for up to 32 levels. The level of 32 was an arbitrary value assigned by Microsoft, because there was a chance that poor loop structure could cause this to continue infinitely. If the 32-level limit is hit, then the transaction is rolled back to the original calling transaction. Figure 8.9 shows an insert of a product that will exceed this limit with a ReOrderLevel of 160 units. If it is allowed to continue, this will hit 32 levels of recursion; but after 32 levels, the entire transaction is rolled back. The rollback is evident by 0 rows being returned from the select of the inserted product. One recursion counter is shared between stored procedures, functions, triggers, and views, so if a stored procedure performs an insert that fires a trigger, which executes the stored procedure, then this cycle continues for only 16 cycles, setting the recursion counter to 32. Figure 8.9. Transactions are rolled back after 32 recursion levels. To see whether nested triggers are enabled on your server, you can use the following command: USE Master GO sp_configure 'nested triggers' which returns the following: name minimum maximum config_value run_value ----------------------------------- ----------- ----------------------- ----------- nested triggers 0 1 1 1 If you want to change the setting and put it into effect immediately, you can use the following command: sp_configure 'nested triggers', '0' RECONFIGURE If you don't use the RECONFIGURE command, you have to re-start the SQL Server service before the sp_configure command takes effect. If you disable nested triggers, then you might find that you do not get the results that you are hoping for. In the example with the ProductReplenishment table, disabling nested triggers would be very noticeable. Figure 8.10 shows the results. The first trigger is allowed to fire, which creates the order in the ProductReplenishment table ”evident by the Updated Products Table message that is generated by the Update_Stock trigger. The second trigger that should have fired was the trigger on the ProductReplenishment table, which would have updated the UnitsOnOrder value in the Products table. This value is still at 0, so the trigger never fired. Figure 8.10. Disabling nested triggers could have unwanted consequences. EXAM TIP Nested and Recursive Triggers Make sure that you know the difference between the nested triggers server option and the recursive triggers database option. Nested triggers are enabled by default, whereas recursive triggers are disabled by default. Only direct recursion is prevented with the recursive triggers option. Both direct and indirect recursion are controlled with the nested triggers option. If you want to see the level of nesting in the current trigger chain, you can use TRIGGER_NESTLEVEL . This command might look like this: IF ((SELECT TRIGGER_NESTLEVEL() ) > 10 ) PRINT 'This statement nested over 10 levels of triggers.' This command returns the total number recursion levels in the current trigger chain, but not the total level of recursion for non-trigger recursion. If non-trigger items are in the chain, then TRIGGER_NESTLEVEL will return the number of triggers that executed, although the recursion level could be much higher. If you specify a trigger object_ID as a parameter, then it tells you how many times that specific trigger fired. The next section looks at security implications with triggers, such as using triggers to control data modifications.  |
EAN: N/A
Pages: 228