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

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:

Get the largest TRAN_SEQ in the TRANS table – this indicates the last row created
Get the ITEM_No from that TRANS record
Using the ITEM_No from the TRANS record, get Assign_Loc from the ITEM table
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

SET QUOTED_IDENTIFIER ON

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