locked
Triger Does not operate on individual records of group Delete or Insert RRS feed

  • Question

  • I appreciate any suggestion on my problem.
    I use SQL Server 2000. I have an tblInventory table(StoreNo,Code,Qty).
    I defined 3 trigers on this table for Insert, Update and Delete. The Trigers simply Log any change in tblInventory into another table named tblInventoryActionLog.
    When I Add/Delete records one by one, the trigers work fine and for each record that has been added or deleted, they add a record to my Log table.

    I use a temp table to do some processing on inventory records, so I transfer the inventory records of each store to the temp table, and after the processing copy them back to tblInventory.
    So I delete a group of records with DELETE query:

    -- (1) Transfering a specific store information to Temp
    Insert Into Temp
    Select * From tblInventory Where StoreNo=1;

    -- (2) Delete the Old data (Group Delete)
    Delete From tblInventory Where StoreNo=1;

    (3)
    Do my processing in Temp table and update the data
    . . .


    --(4) Insert the new inventory (Group Insert)
    Insert Into tblInventory
    Select * From Temp

    So I did a group Delete and a group Insert. I expected that for each record that I have deleted or Inserted, the Triger insert a record into the Log, I mean If I deleted 2000 record with query(2), I expected to have 2000 record in my Log table, which I don't have.
    For every group Delete and Insert it only insert ONE record into the Log table.

    How can I make the triger insert one record for each record in the deleted group?


    Just for more clarity I bring the Trigers here:

    ----------------------------------------------------------------------
    CREATE TRIGGER LogDelete ON [dbo].[tblFrameInventory]
    FOR Delete
    AS

    Declare @OldQty as int
    Declare @StoreNo as char(3)
    Declare @Code as varchar(10)

    Select @OldQty=Qty From Deleted
    Select @StoreNo=StoreNo From Deleted
    Select @Code=code From Deleted

    Insert Into tblInventoryQtyLog (StoreNo, Code, OldQty, NewQty, AddedQty, tDate, Descr)
    Values(@StoreNo, @Code, @OldQty,  0, -@OldQty, getdate(),'Trigger-(*Record Deleted*)')

    ---------------------------------------------------------------------------

    CREATE TRIGGER LogInsert ON [dbo].[tblFrameInventory]
    FOR Insert
    AS
    Declare @NewQty as int
    Declare @StoreNo as char(3)
    Declare @Code as varchar(10)

    Select @NewQty=Qty From Inserted
    Select @StoreNo=StoreNo From Inserted
    Select @Code=Code From Inserted

    Insert Into tblInventoryQtyLog (StoreNo, Code, OldQty, NewQty, AddedQty, tDate, Descr)
    Values(@StoreNo, @Code, 0,  @NewQty, @NewQty, getdate(),'Trigger-(Initial Value)')

    ----------------------------------------------------------------------

    CREATE TRIGGER LogUpdate ON [dbo].[tblFrameInventory]
    FOR UPDATE
    AS
    Declare @NewQty as int
    Declare @OldQty as int
    Declare @StoreNo as char(3)
    Declare @Code as varchar(10)

    Select @NewQty=Qty From Inserted
    Select @OldQty=Qty From Deleted
    Select @StoreNo=StoreNo From Inserted
    Select @Code=code From Inserted

    Insert Into tblInventoryQtyLog (StoreNo, Code, OldQty, NewQty, AddedQty, tDate, Descr)
    Values(@StoreNo, @Code, @OldQty,  @NewQty, @NewQty-@OldQty, getdate(),'Trigger-(Update)')
    Tuesday, June 17, 2008 6:12 PM

Answers

All replies