Insert Into Select only triggers first row

Transact-SQL
1

I have an "Insert Into Select" statement that inserts a number of rows.
The destination table which is the same as the source table have an insert trigger to insert rows in another table.
My problem is that the trigger is only run for the first inserted row.

2

The trigger is written incorrectly. It's probably using variables to store the values.

Instead it needs to SELECT from the inserted table directly instead of using variables.

SQL Server triggers do NOT fire for each row, only ONCE for the entire statement, i.e., ALL rows get passed to the trigger, via the inserted table, at one time.

3

Yes, variables is the problem, you learn every day. The I guess I have a problem here.
The number of rows to be inserted by the trigger varies so I don't know how to do this without using variables.

The trigger:
DECLARE @id nvarchar(20)
DECLARE @dt int
DECLARE @nv int
DECLARE @i int

Select @id = ID FROM INSERTED
Select @dt = DEVICETYPE FROM INSERTED
Select @nv = NOOFVALUES FROM DEVICETYPES WHERE @dt = DEVICETYPE 

Set @i = 1
WHILE @i <= @nv
BEGIN
	INSERT INTO dbo.DEVCHANNELINFO (ID_DEVICE,VALUEINDEX,OFFSET,COMMENT) VALUES (@id,@i,0,'')
	Set @i = @i + 1
END
4

Luckily set-based methods make the trigger much easier to write. I don't know the exact details of how you determine VALUEINDEX and OFFSET, but something like this is what you want:

CREATE TRIGGER ...
ON ...
AFTER INSERT ...
AS
SET NOCOUNT ON;
INSERT INTO dbo.DEVCHANNELINFO (ID_DEVICE,VALUEINDEX,OFFSET,COMMENT)
SELECT ID, DEVICETYPE, ?, ?, ''
FROM inserted
/* end of trigger */
GO

5

VALUEINDEX is just a number from 1 to NOOFVALUES, indexing the rows inserted for this ID_DEVICE. OFFSET is just zero as null is not allowed, it will be chnaged later.
I want NOOFVALUES rows to be inserted, but I don't see how the SELECT part will give me multiple rows

6

Correct, it won't. But you've provided so little data about what the trigger data is that I don't how to write that part for you.

You just need a "tally table", i.e., a table of sequential numbers. Then in the INSERT, join on the tally table where the tally value is between 1 and the total number of rows you need. This will generate the additional rows you want for the INSERT.

7

Brilliant, that will do the trick.
Thank you