在SQL Server中编写一个Insert Insert Trigger

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.

有一个After Insert触发器。正在写入的原始表具有将采用任何数字的数量字段。但是,在插入之后触发的触发器必须为每个QTY写出一次事务表。因此,如果原始QTY为4,那么当触发器触发时,它必须将记录写入四次。

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

如果我不关心为每个写1条记录,那就是用QTY 3写1条记录。我猜@localqty变量是我的问题。我是关闭还是有人可以引导我朝着正确的方向前进

Thanks

2 个解决方案

#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:

#2

Replace this ugly cursor with this simple one select query ........

用这个简单的一个选择查询替换这个丑陋的光标........

insert BLAH.dbo.Transactions ([Status], Area, Location, Item, [level], Quantity, TransTime,
                            [Source],Lot,[ExpireDate],RecvDate,UserID,[Weight],Temperature,
                           Reference,CoolCode,Serial,ToArea,ToLocation) 
SELECT  'New',
         NULL,
         NULL, 
         i.Item_num, 
         i.Lvl, 
         1, 
         Getdate(), 
         'A', 
      -- @LOT,  alien variable dont know where this came from   
         NULL, 
         getdate(), 
         i.Logged_in,
         NULL,
         NULL,
         i.TransiD,
         Null,
         Null, 
         'RTN',
          '1'
FROM    Inserted i CROSS APPLY (SELECT TOP (i.Quantity) t.number
                                FROM master..spt_values t 
                                CROSS JOIN master..spt_values t2)t(number)
Where   i.disposition = 'RTS'

#1