Using Cursors to Solve Multirow Actions in Triggers
In many cases, dealing with multirow operations inside triggers is not an easy task. If the single-row solution is solved, you can use cursors to convert multirow operations into single-row operations inside the trigger, to apply to them the same proved logic of the single-row cases.
Consider the following example: You want to assign a credit limit to every customer following an automated process applied by the AssignCreditLimit stored procedure. To automate the process, you can create a trigger AFTER INSERT to calculate the credit limit for every new customer.
The AssignCreditLimit stored procedure can work with only one customer at a time. However, an INSERT operation can insert multiple rows at the same time, using INSERT SELECT.
You can create the trigger with two parts; one will deal with single row and the other with multiple rows, and you will check which part to apply using the result of the @@ROWCOUNT function as described in Listing 12.23.
Code Listing 12.23. Using Cursors to Convert Multirow Operations into Single-Row Operations Inside Triggers
|
USE Northwind
GO
ALTER TABLE Customers
ADD CreditLimit money
GO
CREATE PROCEDURE AssignCreditLimit
@ID nvarchar(5)
AS
-- Write here your own CreditLimit function
UPDATE Customers
SET CreditLimit = 1000
WHERE CustomerID = @ID
GO
CREATE TRIGGER isr_Customers
ON Customers
FOR INSERT AS
SET NOCOUNT ON
DECLARE @ID nvarchar(5)
IF @@ROWCOUNT > 1
-- Multirow operation
BEGIN
-- Open a cursor on the Inserted table
DECLARE NewCustomers CURSOR
FOR SELECT CustomerID
FROM Inserted
ORDER BY CustomerID
OPEN NewCustomers
FETCH NEXT FROM NewCustomers
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Assign new Credit Limit to every new customer
EXEC AssignCreditLimit @ID
FETCH NEXT FROM NewCustomers
INTO @ID
END
-- close the cursor
CLOSE NewCustomers
DEALLOCATE NewCustomers
END
ELSE
-- Single row operation
BEGIN
SELECT @ID = CustomerID
FROM Inserted
IF @ID IS NOT NULL
-- Assign new Credit Limit to the new customer
EXEC AssignCreditLimit @ID
END
GO
-- Test it
INSERT customers (CustomerID, CompanyName)
VALUES ('ZZZZZ', 'New Company')
SELECT CreditLimit
FROM Customers
WHERE CustomerID = 'ZZZZZ'