Writing an after Insert Trigger in SQL server

Question

Have a After Insert trigger. the original table being written to has a quantity field that will take any number. However the trigger getting fired after the insert must write out to a transaction table once for each of the QTY. So if original QTY is 4 then when the trigger fires it must write the record four times.

USE [BLAH]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[Track_Change_Detail]   
   ON  [dbo].[MYtABLE]
   AFTER Insert
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @Reason varchar(255),
    @TransID  nvarchar(10),
    @Qty bigint,
    @UserID nvarchar(10),
    @Disp   nvarchar(3),
    @ItemNumber nvarchar(20),
    @ItemLevel nchar(1),
    @LocalQTY   Int

    Declare @curChg Cursor
    begin
        --insert into BLAH.dbo.Transactions
        set @curChg = CURSOR FAST_FORWARD FOR
        SELECT              inserted.TransiD, 
                            inserted.Item_num, 
                            inserted.Quantity,
                            inserted.Logged_in,
                            Inserted.Lvl,
                            Inserted.Disposition 

        FROM                inserted
        Where               Inserted.disposition = 'RTS'


        OPEN @curChg
        if (@@error != 0) goto EndError

        fetch next from @curChg into @Transid,@ItemNumber,@QTY,@UserID,@Itemlevel,@Disp

        if (@@error != 0) goto EndError

        while @@FETCH_STATUS = 0
        begin
        Set @LocalQTY = 0
        --if @Disp = 'RTS'
            while @localQTy <= @Qty

            insert BLAH.dbo.Transactions ( 
                                                [Status],
                                                 Area, 
                                                 Location, 
                                                 Item, 
                                                 [Level], 
                                                 Quantity, 
                                                 TransTime, 
                                                 [Source], 
                                                 Lot, 
                                                 [ExpireDate], 
                                                 RecvDate, 
                                                 UserID, 
                                                 [Weight], 
                                                 Temperature, 
                                                 Reference, 
                                                 CoolCode, 
                                                 Serial, 
                                                 ToArea, 
                                                 ToLocation) 
                values                          (
                                                'New',
                                                 NULL,
                                                 NULL, 
                                                 @ItemNumber, 
                                                 @ItemLevel, 
                                                 1, 
                                                 Getdate(), 
                                                 'A', 
                                                 @LOT, 
                                                 NULL, 
                                                 getdate(), 
                                                 @UserID,
                                                 NULL,
                                                 NULL,
                                                 @TransID,
                                                 Null,
                                                 Null, 
                                                 'RTN',
                                                  '1')

            Set @LocalQTY =+1
            if @localQTY = @QTY goto enderror
            fetch next from @curChg into @Transid,@ItemNumber,@QTY,@UserID,@Itemlevel,@Disp
            if (@@error != 0) goto EndError
        end

        close @curChg
        deallocate @curChg

    end

EndError:

END

if I don't care about writing 1 record for each it works by writing 1 record with QTY 3. I am guessing the @localqty variable is my issue. Am I close or can someone steer me in right direction

Thanks

Answer 1

Here is an example of how you can do this without a cursor:

create table log(id int, qty int)
insert into log values
(1, 5),
(2, 3),
(3, 10)


select * from log
cross apply
( select top(log.qty) 1 as d from
 (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) cross join
 (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) cross join
 (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n))ca

Output:

id  qty
1   5
1   5
1   5
1   5
1   5
2   3
2   3
2   3
3   10
3   10
3   10
3   10
3   10
3   10
3   10
3   10
3   10
3   10