Item 1, Unassigned tag processing was partially done via trigger. However there are some problems. For Item 2, Add a flag, I added the field to the table already, just need it populated by logic. I need to step away for a couple of hours, and we can discuss when I get back if you are interested.
1. Unassigned tag processing
a. Process: When new tags are read in (for example, Zone 1) the new record is written to ASSET_CURRENT_DATA table. (current process).
b. At the time the new record is written to ASSET_CURRENT_DATA, I would like to check to see if this tag is already associated with an asset in the Asset Master (TBL_ASSET_DATA). If the tag is not associated with an existing asset, I would like to (all zones)
2. Add a Flag to ASSET_CURRENT_DATA table to identify current location
a. I know this is redundant, because we can infer the current location where datetime_out IS NULL, however, I think I would like to request a field with a distinct value that indicates which row is the current location of the tag.
b. Maybe add new field, “current” with this kind of logic: If datetime_out IS NULL, then current = 1, else, current = 0. It would need to be added to the procedure you have that adds data from ASSET_TAG_DATA to ASSET_CURRENT_DATA table.