Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Trigger tip

A common mistake when first creating a trigger is thinking that the inserted or deleted tables will contain only one row.  This is not true.  However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table.  So you must code the trigger to handle multiple rows.  In the forums of SQLTeam.com, we often see trigger code like this:

CREATE TRIGGER trg_Table1
ON Table1
For UPDATE
AS

DECLARE @var1 int, @var2 varchar(50)

SELECT @var1 = Table1_ID, @var2 = Column2
FROM inserted

UPDATE Table2
SET SomeColumn = @var2
WHERE Table1_ID = @var1

The above trigger will only work for the last row in the inserted table.  This trigger can be fixed by looping through the inserted table, but we all know that we should use a set-based solution instead.  Here's a set-based solution:

CREATE TRIGGER trg_Table1
ON Table1
FOR UPDATE
AS

UPDATE t2
SET SomeColumn = t1.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID

This is a simple example.  Imagine designing a complex trigger under the assumption that the trigger fires once for each row affected by the DML statement.  Now imagine realizing you must redesign it to handle multiple rows.  Here's an example.  The problem with this trigger is that the trigger table is used in a subquery.  Subqueries only allow one row to be returned when it “follows =, !=, <, <= , >, >= or when the subquery is used as an expression“ (from the error message).  This trigger is already in production, so you can imagine the scrambling being done now. 

So if you are new to triggers and find yourself needing to create one, make sure it can handle multiple rows in the trigger tables.  If you can't figure out a set-based solution to do it, post a question here.

Print | posted on Tuesday, September 14, 2004 3:54 PM | Filed Under [ SQL Server - General ]

Feedback

Gravatar

# re: Trigger tip

Hey!

Where's the CURSOR example?

8-)

9/16/2004 10:33 AM | Brett
Gravatar

# re: And I think I'll call it ....

10/6/2004 11:00 AM | The Daily WTF
Gravatar

# re: Trigger tip

Great tip! I never knew this. I thougth I was going crazy because my Trigger was firing sporadically. I never know the virtual table held all the rows, I always naively thought the trigger fired for every row.

Thank you so much for the tip!
8/5/2005 8:31 AM | anon
Gravatar

# re: Trigger tip

Great post - but what about an Instead of Trigger For Inserts?

In this case I cannnot Join to anything ... can I??

Thanks!
8/11/2005 11:14 AM | RichDef
Gravatar

# re: Trigger tip

Thanx so much, I was banging my head against the wall for like 30 min
10/14/2006 3:09 PM | Oliver
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET