How to loop through records within a trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to loop through records within a trigger?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-08 : 17:22:23

Mike Viens writes "


The record structure
--------------------
exhibCatID        - (primary key)
description       - (description)
parentExhibCatID  - (parent exhibCatID or null)
compositeID       - (parent compositeID + "." + description)
isActive          - (0 or 1)

I need to be able to select and order fields by the correct parent/child order, then sorted alphabetically by description, which is only a small sub-select of a much larger, more complicated, multi-joined query.

On insert/update, I need to take the value of a field, and create a composite ID out of it. The target field is a varchar(5000). When a field is inserted/updated (with no parent), then composite = description. When a field is inserted/updated (with a parent), then composite = parentDesc + description.

I have this working as expected. However, it fails when the transaction contains more than one record. I understand why the code fails and am very willing to fix it, but I need an example of how to loop through all records in a transaction.

Below is the version that works for a single record. Could you please advise me the best way to do this?


CREATE TRIGGER exhib_categories_update_trig
  ON dbo.exhib_categories
FOR INSERT, UPDATE
  AS
DECLARE @currentRecord varchar(20), @currentRecordID int, @currentDescription varchar(100), @currentParentExhibCatID int
DECLARE @parentCompositeID varchar(5000), @newCompositeID varchar(5000)

SET @currentRecordID = (SELECT exhibCatID FROM inserted)
SET @currentRecord = CAST(@currentRecordID AS varchar(20))
SET @currentDescription = (SELECT description FROM inserted)
SET @currentParentExhibCatID = (SELECT parentExhibCatID FROM inserted)
SET @parentCompositeID = (SELECT ec.compositeID FROM inserted i, exhib_categories ec WHERE ec.exhibCatID = i.parentExhibCatID)

IF @currentParentExhibCatID IS NULL
  BEGIN
    SET @newCompositeID = UPPER(@currentDescription)
  END
ELSE
  BEGIN
    SET @newCompositeID = @parentCompositeID + '.' + UPPER(@currentDescription)
  END
UPDATE exhib_categories SET compositeID = @newCompositeID WHERE exhibCatID = @currentRecord

"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources