SQL Server Magazine

Editor's Note: Congratulations to SQL Server MVP Fernando G. Guerrero, who submitted a T-SQL Black Belt solution that contributing editor Itzik Ben-Gan used in this article. Fernando will receive $100 and a T-SQL Black Belt shirt. Send your experts-only T-SQL tips to Itzik Ben-Gan at blackbelt@sqlmag.com.

INSTEAD OF triggers, which Microsoft introduced with SQL Server 2000, replace the modification action that a user sends to a table or a view. Like AFTER triggers, INSTEAD OF triggers have access to the Inserted and Deleted tables. But unlike AFTER triggers, which hold the before and after images of the rows that the modification already affected, INSTEAD OF triggers hold what would have been the before and after images of the rows if the change had taken place. Keep in mind that the user action doesn't affect the base table unless the INSTEAD OF trigger performs an action against it. The INSTEAD OF trigger provides new options that you can use to extend the overall functionality of your database and supplies neat solutions to problems that otherwise would be hard to solve. SQL Server MVP Fernando G. Guerrero provided the idea for one of the solutions that I present in this article.

Firing Per-Row AFTER Triggers in Multirow Actions
First, let's look at how to use an INSTEAD OF trigger to address the problem that occurs when you use a regular AFTER trigger that supports only single-row inserts. A third-party solution provider developed this AFTER trigger and encrypted it so that no one could access its source code. Now, suppose you want to allow multirow inserts to your table, but these inserts fail. You can't drop or disable the trigger because you need the trigger to perform its tasks. You simply want to make the trigger support multirow inserts. To see how the trigger works in such a situation, run the code in Listing 1, which creates the T1 table and an AFTER trigger that prints the data of the inserted row. Now try to insert one row:

INSERT INTO T1 VALUES(10)

You'll get the following output:

Who are you?
I'm 10

Try to insert three rows at once:

INSERT INTO T1
  SELECT 20 AS data_col
  UNION ALL
  SELECT 30 AS data_col
  UNION ALL
  SELECT 40 AS data_col

And you'll get the following error:

Server: Msg 512, Level 16, State 1, Procedure trg_T1_i_halo, 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.

If the table didn't have an IDENTITY column, the INSTEAD OF trigger that Listing 2 shows would have provided a solution to the problem. The trigger would simply loop through all the key values in the inserted table and insert the rows, one at a time, into the base table. This action would cause the AFTER trigger to fire separately for each row. Note that the trigger in Listing 2 provides multirow support with single-row triggers on tables that don't have an IDENTITY column. The problem with this solution is that it doesn't work for the T1 table in this example because the key_col column is an IDENTITY column. The key_col column in the Inserted table has a zero value in all the rows because the modification never reaches the table when the trigger's code runs. To solve this problem, you can use the trick that Listing 3 shows. This code uses the IDENTITY() function to generate your key values in a temporary table. You populate the temporary table with the data from the Inserted table and from the newly generated keys. Now the trigger has logic similar to the previous trigger, but the new trigger's loop runs against the temporary table instead of against the Inserted table.

   Prev. page   [1] 2     next page