A requirement arises in many systems to update multiple SQL database rows. For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement. There may be instances where one wants to update a field value for a large, select group of records. Doing this one record at a time is a killer on productivity. Create DML Triggers to Handle Multiple Rows of Data • • 3 minutes to read • Contributors • • In this article THIS TOPIC APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse When you write the code for a DML trigger, consider that the statement that causes the trigger to fire can be a single statement that affects multiple rows of data, instead of a single row. This behavior is common for UPDATE and DELETE triggers because these statements frequently affect multiple rows. The behavior is less common for INSERT triggers because the basic INSERT statement adds only a single row. However, because an INSERT trigger can be fired by an INSERT INTO ( table_name) SELECT statement, the insertion of many rows may cause a single trigger invocation. Multirow considerations are especially important when the function of a DML trigger is to automatically recalculate summary values from one table and store the results in another for ongoing tallies. Note We do not recommend using cursors in triggers because they could potentially reduce performance. To design a trigger that affects multiple rows, use rowset-based logic instead of cursors. Examples The DML triggers in the following examples are designed to store a running total of a column in another table of the AdventureWorks2012 sample database. Storing a running total for a single-row insert The first version of the DML trigger works well for a single-row insert when a row of data is loaded into the PurchaseOrderDetail table. An INSERT statement fires the DML trigger, and the new row is loaded into the inserted table for the duration of the trigger execution. The UPDATE statement reads the LineTotal column value for the row and adds that value to the existing value in the SubTotal column in the PurchaseOrderHeader table. The WHERE clause makes sure that the updated row in the PurchaseOrderDetail table matches the PurchaseOrderID of the row in the inserted table. -- Trigger is valid for single-row inserts. USE AdventureWorks2012; GO CREATE TRIGGER NewPODetail ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = SubTotal + LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID; B. Storing a running total for a multirow or single-row insert For a multirow insert, the DML trigger in example A might not operate correctly; the expression to the right of an assignment expression in an UPDATE statement ( SubTotal + LineTotal) can be only a single value, not a list of values. Therefore, the effect of the trigger is to retrieve a value from any single row in the inserted table and add that value to the existing SubTotal value in the PurchaseOrderHeader table for a specific PurchaseOrderID value. This operation might not have the expected effect if a single PurchaseOrderID value occurred more than one time in the inserted table. To correctly update the PurchaseOrderHeader table, the trigger must allow for the chance of multiple rows in the inserted table. You can do this by using the SUM function that calculates the total LineTotal for a group of rows in the inserted table for each PurchaseOrderID. The SUM function is included in a correlated subquery (the SELECT statement in parentheses). This subquery returns a single value for each PurchaseOrderID in the inserted table that matches or is correlated with a PurchaseOrderID in the PurchaseOrderHeader table. -- Trigger is valid for multirow and single-row inserts.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
July 2018
Categories |