CODEWITHSUNDEEP
sqlsql-server-2005
gruber
gruber

Reputation: 29729

deleted value of the after delete trigger

I've got problems with my triggers (add and delete):

ALTER TRIGGER [trgAfterShoppingInserted]
ON [ShopingList] AFTER INSERT 
AS BEGIN
   DECLARE @cardId char(6), @points int, @userId int

   SET @cardId = (SELECT cardId FROM inserted)
   SET @points = (SELECT points FROM inserted)
   SET @userId = (SELECT userId from [Card] WHERE id = @cardId)
   IF @userId = 0
   BEGIN
   Update [Card]
   set points = points + @points
   where id =@cardId
   END
   ELSE
   Update [Card]
   set points = points + @points
   where id =@cardId OR
   userId = @userId
END


ALTER TRIGGER [trgAfterShoppingDeleted]
ON [ShopingList] AFTER DELETE 
AS BEGIN
   DECLARE @cardId char(6), @points int, @userId int

   SET @cardId = (SELECT cardId FROM inserted)
   SET @points = (SELECT points FROM inserted)
   SET @userId = (SELECT userId from [Card] WHERE id = @cardId)
   IF @userId = 0
   BEGIN
   Update [Card]
   set points = points - @points
   where id =@cardId
   END
   ELSE
   Update [Card]
   set points = points - @points
   where id =@cardId OR
   userId = @userId
END

The problem is on the SET @cardId..

The error is:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

how is it possibile ?

thanks for any help

Upvotes: 1

Views: 323

Answers (2)

marc_s
marc_s

Reputation: 754488

Both triggers will not work if your insert or delete statement should ever insert or delete multiple rows. You need to stop assuming that your trigger only deals with a single row - that's just not the case. You need to rewrite your triggers to handle multiple rows at once (in the Inserted and Deleted tables)

As an example - you could rewrite your trgAfterShoppingDeleted something like this: 

ALTER TRIGGER [trgAfterShoppingDeleted]
ON [ShopingList] AFTER DELETE 
AS BEGIN
   UPDATE [Card]
   SET points = points - i.points
   FROM Inserted i
   WHERE Card.id = i.id AND i.UserId = 0

   UPDATE [Card]
   SET points = points - @points
   FROM Inserted i
   WHERE i.UserId <> 0 AND (Card.id = i.id OR Card.userId = i.userId)
END

You need to think in sets of data - don't assume single rows in your trigger pseudo tables, and don't do RBAR (row-by-agonizing-row) processing in a trigger.

Upvotes: 2

Vikdor
Vikdor

Reputation: 24124

deleted table contains the records that were deleted as part of a given DELETE statement and can contain multiple rows if the DELETE criteria matched multiple records.

This is what happened in your case and when you tried to select cardId from the deleted table that contained multiple records, the select statement is returning multiple values and so the trigger is throwing that exception.

Upvotes: 1

Related Questions