CODEWITHSUNDEEP
sql-serverarraysloopstriggers
b0x0rz
b0x0rz

Reputation: 3981

Array and loop in a SQL Server trigger, how to?

I am creating a trigger on a table in a SQL Server database.

But what I am unable to do is this: I have variables @pageid and @templateid populated, and I need to read all template column id's from one table - basically get a list of them (they depend on the template id and can vary in number) so I need some way of making an array or something and go through it and add it to a table.

So...

  1. how do I read all template id's (image below: from 1st table (available template fields) - where availabletemplateid is @templateid) into an array?

  2. how do I loop through this array and add them one by one it to another table (image below: 2nd table - (public page to field mapping) with publicpageid = @pageid and fieldid is one by one from the previous step, and field content is "-")

enter image description here

So far I have

ALTER TRIGGER CreatePageFields
ON dbo.PublicPages
FOR INSERT
AS
BEGIN TRANSACTION

/* variables */
DECLARE
    @pageid bigint,
    @templateid bigint

/* insert template fields for this page */
SELECT @pageid = id, @templateid = templateid FROM inserted

/* TODO 1 get all template field id's */
/* TODO 2 insert each templatefieldid / page combination */    

/* execute */
COMMIT TRANSACTION

GO

Thanks a lot

Upvotes: 1

Views: 4767

Answers (2)

Russell Fox
Russell Fox

Reputation: 5435

I don't think you need an array (table variable) or any looping (cursor). Something like this will be much, much faster (guessing at your table and field names):

INSERT INTO publicpagetofieldmapping (PublicPageID, FieldID, FieldContent)
SELECT t1.ID, t2.ID, '-'
FROM INSERTED t1
JOIN AvailableTemplateFields t2
ON t1.TemplateID = t2.availabletemplateid

Upvotes: 4

mdoyle
mdoyle

Reputation: 727

Use a cursor:

DECLARE cur CURSOR FOR
SELECT ID
FROM AvailableTemplateFields
WHERE AvailableTemplateID = @templateid;

FETCH NEXT FROM cur INTO @current_template_id

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO PublicPageFieldMapping (
        PublicPageID, FieldID, FieldContent
    ) VALUES (
        @pageid, @current_template_id, '-'
    );

    FETCH NEXT FROM cur INTO @current_template_id;
END

CLOSE cur
DEALLOCATE cur

EDIT: Although this is the way to do it "with an array" (not that a cursor is an array, but the logic is the same as far as reading DB records into it and then stepping through it), you should also be able to accomplish this with a straight query, which would be faster.

Upvotes: 0

Related Questions