Suppose that you have a table with an AFTER trigger that works well only for single-row modifications and breaks for multirow ones. You need to support multirow modifications, but you’re not allowed to modify the trigger’s code. Maybe the code was developed by a third party with the ENCRYPTION option and you don’t have access to the source code. Or maybe the trigger was created by another developer and you’re not allowed to change it.

To demonstrate an example for a trigger that would work well only for single-row modifications, first run the following code, which creates the table T1:

USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
  keycol  INT NOT NULL PRIMARY KEY,
  datacol INT NOT NULL
);

Run the following code to create an AFTER INSERT trigger called trg_T1_i on T1:

CREATE TRIGGER trg_T1_i ON T1 AFTER INSERT
AS

DECLARE @msg AS VARCHAR(100);
SET @msg = 'Key: '
  + CAST((SELECT keycol FROM inserted) AS VARCHAR(10)) + ' inserted.';
PRINT @msg;
GO

The trigger constructs a message with the keycol value of the row that was inserted and prints it. It uses a subquery to fetch the keycol value from inserted.

If you load a single row to the table, the trigger works well:

INSERT INTO dbo.T1(keycol, datacol) VALUES(1, 10);

You get the output:

Key: 1 inserted.

However, when you issue a multirow insert, the trigger fails:

INSERT INTO dbo.T1(keycol, datacol)
  SELECT 2, 20
  UNION ALL
  SELECT 3, 30
  UNION ALL
  SELECT 4, 40;

The trigger produces the following error:

Server: Msg 512, Level 16, State 1, Procedure trg_T1_i, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,
<, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

The error was generated because a subquery that was used where a scalar value was expected produced multiple values. Assuming that you’re not allowed to alter the trigger’s code, you can tackle the problem by creating an INSTEAD OF INSERT trigger. You can use the technique I showed earlier to iterate through the rows in inserted to collect the values from each row and issue a single row insert for each source row against the target table. Each insert will cause a different instance of the AFTER trigger to fire, but it will fire for a row at a time. Run the code in Example 8-7 to create such an INSTEAD OF trigger.

CREATE TRIGGER trg_T1_ioi_perrow ON dbo.T1 INSTEAD OF INSERT
AS

DECLARE @rc AS INT;
SET @rc = @@rowcount;
IF @rc = 0 RETURN;

IF @rc = 1
  INSERT INTO dbo.T1 SELECT * FROM inserted;
ELSE
BEGIN
  DECLARE @keycol AS INT, @datacol AS INT;

  DECLARE Cinserted CURSOR FAST_FORWARD FOR
    SELECT keycol, datacol FROM inserted;
  OPEN Cinserted;

  FETCH NEXT FROM Cinserted INTO @keycol, @datacol;
  WHILE @@fetch_status = 0
  BEGIN
    INSERT INTO dbo.T1(keycol, datacol)
      VALUES(@keycol, @datacol);
    FETCH NEXT FROM Cinserted INTO @keycol, @datacol;
  END

  CLOSE Cinserted;
  DEALLOCATE Cinserted;
END
GO

The trigger’s code uses the technique I described earlier to apply a course of action that depends on the number of affected rows, only this time using a cursor to iterate through the rows in inserted. If zero rows were affected, the trigger exits. If one row was affected, the trigger loads the row from inserted into T1, causing the AFTER trigger to fire for that row. If multiple rows were affected, the trigger uses a cursor to iterate through the rows in inserted, one at a time, collecting the values from the current row in each iteration, and it then inserts a row to T1 with those values. As a result, the AFTER trigger will fire once for each row.

To test the trigger, first issue the following INSERT statement, which loads a single row to T1:

INSERT INTO dbo.T1(keycol, datacol) VALUES(5, 50);

You will get the output:

Key: 5 inserted.

Try loading multiple rows using a single INSERT statement:

INSERT INTO dbo.T1(keycol, datacol)
  SELECT 6, 60
  UNION ALL
  SELECT 7, 70
  UNION ALL
  SELECT 8, 80;

The statement will run successfully, producing the following output:

Key: 6 inserted.
Key: 7 inserted.
Key: 8 inserted.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;

The following example will demonstrate how to use an INSTEAD OF TRIGGER to support UPDATE statements against a view that would have not been supported otherwise.

Suppose that you have a table with order details and a view that aggregates order detail quantities per order. Run the code in Example 8-8 to create the OrderDetails table, populate it with sample data, and create the VOrderTotals view, which calculates the sum of the quantity for each order.

USE tempdb;
GO
IF OBJECT_ID('dbo.VOrderTotals') IS NOT NULL
  DROP VIEW dbo.VOrderTotals;
GO
IF OBJECT_ID('dbo.OrderDetails') IS NOT NULL
  DROP TABLE dbo.OrderDetails;
GO

CREATE TABLE dbo.OrderDetails
(
  oid  INT NOT NULL,
  pid INT NOT NULL,
  qty INT NOT NULL,
  PRIMARY KEY(oid, pid)
);

INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 1, 10);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 2, 20);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 3, 30);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 1, 5);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 2, 10);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 3, 15);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 1, 20);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 2, 20);
INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 3, 20);
GO

CREATE VIEW dbo.VOrderTotals
AS

SELECT oid, SUM(qty) AS totalqty
FROM dbo.OrderDetails
GROUP BY oid;
GO

Suppose that you want to allow updating the totalqty column and distribute the new value between the order details of the affected order by the same proportions that they had before the update. The trigger shown in Example 8-9 achieves this task.

CREATE TRIGGER trg_VOrderTotals_ioi ON dbo.VOrderTotals INSTEAD OF UPDATE
AS

IF @@rowcount = 0 RETURN;

IF UPDATE(oid)
BEGIN
  RAISERROR('Updates to the OrderID column are not allowed.', 16, 1);
  ROLLBACK TRAN;
  RETURN;
END;

WITH UPD_CTE AS
(
  SELECT qty, ROUND(1.*OD.qty / D.totalqty * I.totalqty, 0) AS newqty
  FROM dbo.OrderDetails AS OD
    JOIN inserted AS I
      ON OD.oid = I.oid
    JOIN deleted AS D
      ON I.oid = D.oid
)
UPDATE UPD_CTE
  SET qty = newqty;
GO

The trigger’s code exits if no rows were updated. It then checks whether there was an attempt to update the order ID (oid column). If there was such an attempt, the trigger generates an error and rolls back the activity. The trigger then creates a common table expression (CTE) that joins inserted, deleted, and the OrderDetails table. The deleted view holds the value of totalqty before the update, and inserted holds the value after the update. The OrderDetails table is added to the join to gain access to the original quantity of each order detail row, and it will also end up being the actual target of the modification. The CTE’s query calculates the portion each order detail row should get based on the original proportions (original quantity / original total quantity × new total quantity, rounded). The outer UPDATE statement updates the qty column in OrderDetails with the newly calculated quantity.

Run the following code to test the trigger:

SELECT oid, pid, qty FROM dbo.OrderDetails;
SELECT oid, totalqty FROM dbo.VOrderTotals;

UPDATE dbo.VOrderTotals
  SET totalqty = totalqty * 2;

SELECT oid, pid, qty FROM dbo.OrderDetails;
SELECT oid, totalqty FROM dbo.VOrderTotals;

The code first queries both OrderDetails and VOrderTotals, then it issues an UPDATE statement, and then it queries OrderDetails and VOrderTotals again. Table 8-5 and Table 8-6 show the contents of the table and view, respectively, before the change. Table 8-7 and Table 8-8 show the contents of the table and view, respectively, after the change.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.VOrderTotals') IS NOT NULL
  DROP VIEW dbo.VOrderTotals;
GO
IF OBJECT_ID('dbo.OrderDetails') IS NOT NULL
  DROP TABLE dbo.OrderDetails;

Suppose that you maintain your own custom sequence instead of relying on the IDENTITY column property to generate numbers that will be used as keys in a table called T1. You achieve this by holding a value in a table called Sequence, and the value represents the last assigned sequence value. Whenever you need a new sequence value, you increment the value in the Sequence table and use it as the new key in T1.

Run the following code to create the table T1:

SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol  INT NOT NULL PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
);

Then run the following code to create and initialize the Sequence table:

IF OBJECT_ID('dbo.Sequence') IS NOT NULL
  DROP TABLE dbo.Sequence;
GO
CREATE TABLE dbo.Sequence(val INT NOT NULL);
INSERT INTO dbo.Sequence VALUES(0);

When you need a new sequence value, you can issue the following code:

DECLARE @key AS INT;
UPDATE dbo.Sequence SET @key = val = val + 1;

This code declares a local variable called @key, issues a specialized UPDATE statement that increments the val column value by one, and stores the result in the variable @key. You’re effectively both updating and selecting the sequence value at the same time in an atomic operation. Suppose that you want to automate the process of assigning keys to new rows using a trigger, and you also want to support multirow inserts. An AFTER trigger would not be a good choice because the primary key will reject an attempt to load multiple rows where the key values are not specified.

Remember that an INSTEAD OF trigger is fired instead of the original modification; therefore, such a trigger will allow a multirow insert where the keys weren’t specified. All rows will get the default value 0, and the trigger can generate a different key for each row. The trigger will then insert rows into T1 with newly generated keys, while the rest of the columns (only datacol in our case) can be grabbed from inserted.

Here’s the code of the INSTEAD OF trigger that will achieve the task–generating new keys and loading them along with the other attributes from the original INSERT statement:

CREATE TRIGGER trg_T1_ioi_assign_key ON dbo.T1 INSTEAD OF INSERT
AS

DECLARE @rc AS INT, @key AS INT;
SET @rc = @@rowcount;

IF @rc = 0 RETURN; -- if 0 affected rows, exit

-- Update sequence
UPDATE dbo.Sequence SET @key = val, val = val + @rc;

INSERT INTO dbo.T1(keycol, datacol)
  SELECT @key + ROW_NUMBER() OVER(ORDER BY const), datacol
  FROM (SELECT 1 AS const, datacol FROM inserted) AS I;
GO

The trigger’s code first checks whether zero rows were loaded. If that’s the case, it exits. The code then uses an UPDATE statement to assign the current value of the sequence to the variable @key and increment the sequence value by the number of affected rows–that is, it acquires a whole block of sequence values in one shot. Next, the trigger issues an INSERT statement that loads all rows from inserted into T1, generating new key values by adding a row number value to @key. The ORDER BY clause of the ROW_NUMBER function has no effect on the assignment order of the row numbers. The value of const will be 1 in all rows anyway. The reason for using this ORDER BY clause is that it’s not optional in the ROW_NUMBER function. Of course, if you want to assign keys based on some specific order, specify the relevant attribute in the ORDER BY clause.

To test the trigger, issue the following INSERT statement:

INSERT INTO dbo.T1(datacol)
  SELECT LastName FROM Northwind.dbo.Employees;

If you query T1, you will get the output shown in Table 8-9:

SELECT keycol, datacol FROM dbo.T1;

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
IF OBJECT_ID('dbo.Sequence') IS NOT NULL
  DROP TABLE dbo.Sequence;