Counting records inside a trigger
-
I'm writing my first triggers, so please take that into account.
In a parent table, I am maintaining a record count of child records so I can display the count without having to calculate it all the time. I have written two triggers in the child -- one to increment the count on Insert and one to decrement the count on Delete. For some reason, they aren't working and I'm not sure how to debug them.
I'll post the code for one of the triggers here, just to see if anything obvious jumps out at anyone. (Or if you see any bad practices.) More importantly, I am interested in learning how to go about debugging a trigger like this.
The situation is a Product to Category relationship which is a M-M. I am storing the ItemCount in the Category table, and counting how many active records there are in the category.
Any help would be much appreciated!
CREATE TRIGGER [CATEGORIES_PRODUCTS_INSERT] ON [dbo].[categories_products]
FOR INSERT
AS
BEGIN
DECLARE
@idCategory INT,
@cCategory CURSOR
SET @cCategory = CURSOR FOR
SELECT idCategory
FROM INSERTED
WHERE idProduct IN (SELECT idProduct FROM Products WHERE active<0)
OPEN @cCategory
FETCH NEXT FROM @cCategory INTO @idCategory
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE categories SET itemCount = itemCount+1 WHERE idCategory = @idCategory
FETCH NEXT FROM @cCategory INTO @idCategory
END
CLOSE @cCategory
DEALLOCATE @cCategory
END
-
Rule 1: You never need to use cursors
Rule 2: If you need to use cursors, refer to rule 1
j/k but in this case, you certainly don't need a trigger.A few questions first.
Why is it necessary to keep a count in the parent table? Can't you calc the count when it is required.
Can you not use a calculated field?
Now, as for that trigger...
CREATE TRIGGER [CATEGORIES_PRODUCTS_INSERT] ON [dbo].[categories_products]
FOR INSERT
AS
BEGIN
UPDATE categories SET itemCount = itemCount +
(SELECT Count (idCategory) FROM inserted INNER JOIN Products ON inserted.idProduct = Products.idProduct WHERE active<0)
END
As for your question on how you debug triggers, the only answer I have is: With great difficulty.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi GilaMonster,
Thanks for your help and advice. I sort of expected someone to comment on the cursor thing.
The reason I don't want to calc these when they are required is because the categories and their counts display on every page on my site, and I figured I would save the db a whole lot of work if I don't calc them every single page display. I figured that just decrementing the count with each delete and incrementing it with each insert was a simple solution.
I have to admit I know nothing about calculated fields. I'll go investigate now, but would this be an appropriate usage?
Thanks again!
-
I've never used calculated fields myself, but from what I understand they update only when the data changes, so it may save you from writing a trigger in the first place.
For more info, you'll have to look in BoL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I still can't get this to work for some reason. I modified it to update the correct category like so:
CREATE TRIGGER [CATEGORIES_PRODUCTS_INSERT] ON [dbo].[categories_products]
FOR INSERT
AS
BEGIN
UPDATE categories SET itemCount = itemCount + (SELECT Count (idCategory) FROM inserted INNER JOIN Products ON inserted.idProduct = Products.idProduct WHERE active<0)
WHERE categories.idCategory = (select idCategory from inserted)
END
I have even tried removing the "active<0" clause and it still doesn't do anything. It's almost as if the trigger isn't even firing.
-
How are you doing the insert?
Try putting some print statements into the trigger and do an insert from query analyser. That's about the only way I know to get messages/errors back from the trigger
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply