1. Home
  2. Inventory and Item Tracking Software
  3. BACKTRACK
  4. BACKTRACK Instructions
  5. BACKTRACK Instructions - Database Problem Fixes
  6. Add Trigger to Force BACKTRACK to Allow Check-in to Newly Updated Assigned Location – BACKTRACK Instruction Article
Searching...

Add Trigger to Force BACKTRACK to Allow Check-in to Newly Updated Assigned Location – BACKTRACK Instruction Article

Bookmark(0)

Summary

The client, in this situation, wanted to Check-in an item to a location other than the default Assigned Location value.  We can use Transaction Pre-Authorization to force a change in the Assigned Location field during Check-in, however this happens after the transaction has updated the RELATION table.  Thus reports on the location of the item would still show in the old Assigned Location.

This particular instruction shows how to use ‘Trigger’ in SQL to make BACKTRACK updates the field in RELATION table to match the Assign_Loc in Item table. We have to do this because the application must make allowance for items to be checked-in to a different location than where they originated. We use extra field to update the Assign_Loc but the p_appl_no field in RELATION table does not get properly updated. When running the report, BACKTRACK will pull data from RELATION table instead of ITEM table (even when explicitly stated that the field is from ITEM table).

Instructions

In order to make this work, we have to utilize the trigger mechanism in SQL. We have to be cognizant of the fact that the transaction may involve multiple record updates in the item table at one time.

Initially, we focused on making the code execute after the update operation.  However, that proved to be problematic.  Our tests indicate that this not a reliable method, as we can only the last update.  It seems that BACKTRACK may perform one update operation that updates multiple row in the ITEM table.
We switch to use TRANS table trigger instead.  Since only one record is created for every transaction.  This guarantees that for every record created, we get a trigger.
Trigger Logic:
For each row INSERTED into Trans table:
  1.    Get the largest TRAN_SEQ in the TRANS table – this indicates the last row created
  2.    Get the ITEM_No from that TRANS record
  3.    Using the ITEM_No from the TRANS record, get Assign_Loc from the ITEM table
  4.    Update the P_APPL_No = Assign_Loc (From ITEM) for the record in RELATION table that has the C_ITEM_NO = ITEM_NO
Code to Create the Trigger:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE trigger [dbo].[Update_Relation] on [dbo].[TRANS] AFTER insert AS
    Begin 
       set nocount on
       declare @item varchar(15), @loc varchar(25), @seq varchar(15);
       select @seq=max(TRANS.TRAN_SEQ) from trans                          — Get the largest value for TRAN_SEQ
       select @item = trans.item_no                                                    — Get item number from TRANS
           from trans 
   where trans.tran_seq = @seq
       select @loc = item.assign_loc                                                    — Get Assign_Loc from ITEM
           from item 
   where item_no = @item
       update [common].[dbo].[relation]                                               — Update RELATION with new location value
       set p_appl_no = @loc where c_item_no = @item
    end

Additional Comments

There may be a more efficient way to get SQL to do this. However, this code is much simpler than using Join statements in SQL.

Print Friendly, PDF & EmailPrint / Email This Article
Updated on October 31, 2017
Tagged:

Was this article helpful?

Yes No

About The Author

Dave Klement

Principal at Efficient Business Systems

Related Articles

Need Help?
Click the link below to submit a support ticket
Submit Ticket