April 4, 2011 at 1:34 am
CREATE TRIGGER myTrigger
ON OrderItems
AFTER DELETE
AS
BEGIN
DECLARE @Count INT;
@Count = SELECT [OLD].Itemcount from OrderItems
UPDATE Inventory
SET InventoryCount = InventoryCount + @Count
WHERE Inventory.ItemId IN(SELECT OLD.ItemId FROM OrderItems)
END
I'm getting the following error:
Msg 102, Level 15, State 1, Procedure myTrigger, Line 8
Incorrect syntax near '@Count'.
April 4, 2011 at 1:48 am
I my self found 😀
April 4, 2011 at 1:49 am
CREATE TRIGGER myTrigger
ON OrderItems
AFTER DELETE
AS
DECLARE @Count INT;
SELECT @Count = (SELECT ItemCount from Deleted)
BEGIN
UPDATE Inventory
SET InventoryCount = InventoryCount + @Count
WHERE Inventory.ItemId IN(SELECT ItemId FROM Deleted)
END
The correct one.
April 4, 2011 at 2:12 am
zorbonjo (4/4/2011)
DECLARE @Count INT;SELECT @Count = (SELECT ItemCount from Deleted)
What's going to happen if there is more than one row in the Deleted table?
The correct one.
As long as one and only one row is ever deleted at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 2:44 am
what to do if more than one row is deleted ?
April 4, 2011 at 2:49 am
You need to write the trigger in a set-based way. Assigning variables from inserted or deleted in a trigger is almost a guarantee that the trigger will fail to work correctly for multiple rows.
Think about joining the deleted table to Inventory for the update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 3:34 am
CREATE TRIGGER myInsertTrigger
ON OrderItems
AFTER UPDATE
AS
DECLARE @Count INT;
SELECT @Count = (SELECT ItemCount from UPDATED)
BEGIN
IF(@Count>[OLD].ItemCount)
THEN
UPDATE Inventory
SET InventoryCount = InventoryCount - @Count
WHERE Inventory.ItemId IN(SELECT ItemId FROM UPDATED)
ELSEIF (@Count<[OLD].ItemCount)
THEN
UPDATE Inventory
SET InventoryCount = InventoryCount + @Count
WHERE Inventory.ItemId IN(SELECT ItemId FROM UPDATED)
ELSE
@Count=0
END IF;
END
I tried like the above but error is coming.. any suggestion.?
Errors are:
Msg 156, Level 15, State 1, Procedure myInsertTrigger, Line 10
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure myInsertTrigger, Line 14
Incorrect syntax near 'ELSEIF'.
Msg 156, Level 15, State 1, Procedure myInsertTrigger, Line 19
Incorrect syntax near the keyword 'ELSE'.
April 4, 2011 at 3:56 am
Forget the error for now. (SQL doesn't have an ELSEIF keyword, it doesn't use the THEN keyword in IF..ELSE and there's no Updated table, just inserted and deleted)
If you want the trigger to work with multiple rows, you cannot assign values from inserted or deleted to a variable. It will not work properly if there are multiple rows.
p.s. That's not the same trigger, let's fix the deleted trigger then you should be able to apply the same principals to the others. Post your changes to the deleted trigger and let's get that working correctly.
p.p.s. May I suggest you spend some time with the SQL help file (books online) rather than guessing as to valid syntax.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply