March 8, 2011 at 3:04 pm
Been searching but cant find a problem like mine, maby Iäm bad at searching. Here is the problem:
My delete trigger only fires at one row when there are multible rows deleted. From what I get I need to loop through the virtuel delete table.
For each row deleted I wan't to be able to do som If statement depending on some colums value. Do I have to make an fetch loop?
/Wold love some hint what to look for. 🙂
March 8, 2011 at 3:09 pm
Well, you can work with all the deleted records at once in a trigger, and you should. Can you post your trigger code? Maybe we can help you turn it into something set based.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 8, 2011 at 4:55 pm
You're seeing the correct behaviour - triggers are fired once per batch, not once per row. You can never assume that only a single row will be deleted at a time, so all triggers should be written to handle multiple rows.
You can loop over the deleted table, but could be quite inefficient for large deletes. As Craig said, if you can show use the code, there may be an easy set-based solution.
March 8, 2011 at 5:03 pm
Hope this helps. From a blog posting by Steve Jones (Yes Steve Jones of SSC)
TriggerProper trigger writing/* Steve Jones Blog Posting
Create trigger orders_update_inventory on orders
for update
as
select @qty = a.qty - b.qty
from inserted a
inner join deleted b
on a.orderid = b.orderid
select @product = productid from inserted
update inventory
set onhand = onhand - ( a.qty - b.qty)
from inserted a
inner join deleted b
on a.orderid = b.orderid
where inventory.productid = i.productid
/*
return
Triggers should always be written to handle multiple rows,
using the inserted and deleted tables for joins instead of variables.
Even if you always just update single rows, coding this way will
prevent issues if there is a multiple row change
March 8, 2011 at 8:10 pm
wiklander79 (3/8/2011)
Been searching but cant find a problem like mine, maby Iäm bad at searching. Here is the problem:My delete trigger only fires at one row when there are multible rows deleted. From what I get I need to loop through the virtuel delete table.
For each row deleted I wan't to be able to do som If statement depending on some colums value. Do I have to make an fetch loop?
/Wold love some hint what to look for. 🙂
Heh... post your trigger code and don't even think about looping in a trigger. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2011 at 4:04 am
This is my trigger for insert, It works multi rows and single row. What I do is just count down on avaible article amount.
(some name are in swedish, don't blame me 🙂 what I have to work with)
The delete trigger should do about the same thing as this one but handle multible rows in the the "delete table"
ALTER TRIGGER [dbo].[articlecount]
ON [dbo].[orderitem]
AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @ArticleNR AS NVARCHAR(20),
@antal AS INT
SELECT @ArticleNR = I.artikelnr
FROM INSERTED I
SELECT @antal = (SELECT ISNULL(Antalkvar, 0) FROM Artiklar WHERE ArtikelNr = @ArticleNR)
IF @antal > 0
BEGIN
UPDATE Artiklar
SET [Antalkvar] = Antalkvar - 1
WHERE ArtikelNr = @ArticleNR
END
March 9, 2011 at 4:12 am
Post the current code that you have for the delete trigger please.
p.s. your insert trigger does not work for multiple rows inserted
DECLARE @ArticleNR AS NVARCHAR(20),
@antal AS INT
SELECT @ArticleNR = I.artikelnr
FROM INSERTED I
If there are multiple rows inserted, that will get one of the article numbers. Which one is undefined, but it will only get one.
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
March 9, 2011 at 5:24 am
my bad,, did a multi insert in sql management.. 🙁 sorry,
March 9, 2011 at 5:56 am
Here's a modification of your trigger that will work with multiple rows inserted. I think it's correct, haven't tested.
ALTER TRIGGER [dbo].[articlecount]
ON [dbo].[orderitem]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE Artiklar
SET [Antalkvar] = Antalkvar - 1
WHERE ArtikelNr IN (SELECT artikelnr FROM INSERTED)
AND Antalkvar> 0
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
March 9, 2011 at 6:45 am
awesome will give it a try, Guessing I can use this on delete to?
March 9, 2011 at 6:49 am
Depends on what you want the delete trigger to do. Your description earlier wasn't very clear. Same kind of pattern should be applicable.
One thing that the trigger doesn't take into account... What needs to happen if the same artikelnr is inserted multiple times in one batch? Subtract 1 or subtract the total number of times its specified?
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
March 9, 2011 at 7:21 am
Gila you rock,,, wohoo, Got it to work on both multi insert and delete. Awesome.
Tx so much
/Wiklander
March 9, 2011 at 7:24 am
Pleasure. Just confirm one thing please
What needs to happen if the same artikelnr is inserted multiple times in one batch? Subtract 1 or subtract the total number of times its specified?
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
March 9, 2011 at 7:36 am
It should update 1 per row,. 5 row = (-5)
/Wiklander
March 9, 2011 at 7:53 am
Then that trigger is not correct....
Maybe this
ALTER TRIGGER [dbo].[articlecount]
ON [dbo].[orderitem]
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE Artiklar
SET [Antalkvar] = Antalkvar - TotalArticles
FROM Artiklar INNER JOIN (SELECT COUNT(*) AS TotalArticles, artikelnr FROM INSERTED GROUP BY artikelnr) i
ON Artiklar.ArtikelNr = i.artikelnr
WHERE Antalkvar> 0
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply