10k — Supplemental Triggers (SUPPLEMENTAL_ABSD_*)

Supplemental Table Architecture

Purpose: SUPPLEMENTAL_ABSD_* tables extend the mainframe-era ABSD_ schema by holding fields that could not fit within the original fixed-width record layouts. Each SUPPLEMENTAL table is keyed identically to its corresponding ABSD_ base table (typically LRNO or LRNO+SEQNO).

Trigger pattern: SUPPLEMENTAL triggers typically:

Validate the supplemental field value
Cascade the validated value back to the corresponding field in the base ABSD_ table
Update ABSD_OVGE J06_AUTHOR/J06_LNCHDATE (and sometimes J06_LNCHTIME) — the standard "last-touched" audit trail
Write to tblAnnotationLogGeneral and/or tblChanges for field-level audit
Update ABSD_SHIP_SEARCH for search-optimised fields

Guard anomalies: Several SUPPLEMENTAL DELETE triggers have no TRIGGER_DISABLE guard (HIMA_Delete, STSE_Delete). SuppHITLUpdate has no guard at all — it fires unconditionally even on INSERT/UPDATE.

SUPPLEMENTAL_ABSD_CADI — Scrap Price

SuppCADIUpdate

FOR INSERT, UPDATE

Table

SUPPLEMENTAL_ABSD_CADI

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per LRNO+INNO

TOTAL_SCRAP_PRICE → ABSD_SALE Cascade Logic

When TOTAL_SCRAP_PRICE changes and is > 0, the trigger maintains ABSD_SALE through a three-branch decision tree:

Branch 1 — No SALE record exists: Calls spCreateNewSaleWithPrice(@LRNO, @TOTAL_SCRAP_PRICE) to create a new sale record; then inserts a row into tblFSWSalesPage with status 'X' and flag 1 (replacing any existing row)
Branch 2 — SALE exists, same price already recorded: No action — the price has not changed
Branch 3 — SALE exists, price differs from current SEQ_NO='00' record:
- If SALE_EFD for SEQ_NO='00' is today's date: updates ABSD_SALE.SALE_PRICE_USD in place (same-day correction)
- If SALE_EFD is an earlier date: calls spMakeSALEHistoricalWithPrice(@LRNO, @TOTAL_SCRAP_PRICE) to archive the current record and create a new current record; then refreshes tblFSWSalesPage

CALA Audit Update

Unconditionally updates ABSD_CALA.EDITOR and ABSD_CALA.EDIT_DATE for the matching LRNO+INNO
This keeps the APS production casualty extraction timestamp current

SUPPLEMENTAL_ABSD_CBCOROOT — Shipbuilder Proper-Case Short Name

UpdateShortName

FOR UPDATE NO TRIGGER_DISABLE GUARD

Table

SUPPLEMENTAL_ABSD_CBCOROOT

Guard

NONE — fires unconditionally

Bidirectional Short Name Sync (non-'S' types only)

When SHORT_NAME_PCASE changes on a record where OUT_TYPE ≠ 'S': writes ABSD_CBCOROOT.OUT_SHORT_NAME = UPPER(SHORT_NAME_PCASE)
This is the reverse of what CBCOROOT_Update does (which writes SHORT_NAME_PCASE = dbo.proper(OUT_SHORT_NAME)) — together they maintain both the upper-case canonical and the proper-case display forms
For OUT_TYPE='T' (tender/sub-builder) only: if the builder code exists in ABSD_SHIP_SEARCH, also updates ABSD_SHIP_SEARCH.SHIPBUILDER = SHORT_NAME_PCASE

SUPPLEMENTAL_ABSD_CBCY — Country Codebook with Sanctions

SUPP_ABSD_CBCY_UPD

FOR INSERT, UPDATE

Table

SUPPLEMENTAL_ABSD_CBCY

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per KEY (3-char country code)

LRF Codebook Sync → ABSD_CBCY

If KEY already exists in ABSD_CBCY: updates all 30+ marker/date/reference fields (FDATE, TDATE, PRNT, POSN, MK01–MK20, TROC, RBS1/2, GNST, PSS1/2, DELT)
If KEY does not exist: inserts a new row with all those fields from SUPPLEMENTAL
The SUPPLEMENTAL table is the edit surface; ABSD_CBCY is the downstream copy

Fairplay Codebook Sync → tblFairplayCountryUniqueIMO

If IMO (keyed by SUPPLEMENTAL KEY) already exists: updates Country_Code, Country_Name, Telephone_Dial_Code, Time_Zone, Telex_Code, Country_Descriptor, Area, ISO2, ISO3, ISONo, email_extn
If Country_Code is not NULL and KEY does not exist in Fairplay table: inserts a new row

Sanctions Logic — MK01='C' (Controlled/Sanctioned)

Sanction removed (old MK01='C', new MK01≠'C'): Sets TODATE = GETDATE() on the most recent open sanctions row for this KEY (the row with the latest FROMDATE and no TODATE)
Sanction added (new MK01='C', old MK01≠'C', no open sanctions row): Inserts a new tblSanctionedCountry row with FROMDATE = GETDATE()
The condition ISNULL(TODATE,''） = '' identifies open (current) sanction records

SUPP_ABSD_CBCY_DEL

FOR DELETE NO TRIGGER_DISABLE GUARD

Deletes all open tblSanctionedCountry rows (TODATE IS NULL) for the deleted country keys
Closed sanction rows (with TODATE) are retained for history

SUPPLEMENTAL_ABSD_FUDI1 — Manbow Length

SUPP_FUDI_UPDATE

FOR INSERT, UPDATE

Table

SUPPLEMENTAL_ABSD_FUDI1

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per LRNO

Validates D22_MANBOW_LEN via spValidateManbow_Len stored procedure; if @Errcode=0 (failure), raises the returned error message and rolls back
On successful validation, updates ABSD_FUDI1.D22_MANBOW_LEN for the matching LRNO
J06 audit and annotation for D22_MANBOW_LEN field on SUPPLEMENTAL_ABSD_FUDI1

SUPPLEMENTAL_ABSD_FUDI2 — J06 Audit Only

SUPPLEMENTAL_ABSD_FUDI2_All

FOR UPDATE, INSERT, DELETE

Guard

TRIGGER_DISABLE (standard)

J06_AUTHOR and J06_LNCHDATE audit on ABSD_OVGE only — no validation, no cascade, no staging
Fires on UPDATE, INSERT, and DELETE (most supplemental triggers only fire on INSERT/UPDATE)

SUPPLEMENTAL_ABSD_FUGE — Tank Heat Exchanger

supp_fuge_update

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

On TANK_HEAT_EX change: writes annotation to tblAnnotationLogGeneral for grouping SUPPLEMENTAL_ABSD_FUGE / Tank_Heat_Ex
J06 audit including J06_LNCHTIME (hours, minutes, seconds — more precise than the usual date-only audit)
No validation, no cascade to base table

SUPPLEMENTAL_ABSD_FUUN1 — TEU Capacity

FUUN1_Supp_Upd

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per LRNO

D11_TEU_CAP Cascade with Range Check

If D11_TEU_CAP > 99999: writes 0 to ABSD_FUUN1.D11_TEU_CAP (the ABSD_ base field cannot hold 6-digit values; the supplemental table holds the full value)
If D11_TEU_CAP ≤ 99999: writes actual value to ABSD_FUUN1.D11_TEU_CAP
Also cascades to ABSD_SHIP_SEARCH.TEU for the search index

Annotation

On D11_TEU_CAP change: annotation grouping for SUPPLEMENTAL_ABSD_FUUN1 / D11_TEU_Cap
On TEU_14 change (14-tonne TEU): annotation grouping for SUPPLEMENTAL_ABSD_FUUN1 / TEU_14
Note: old commented-out code used >9999 as the threshold; current trigger uses >99999

SUPPLEMENTAL_ABSD_HIFC1 — Flag/Callsign History (J06 only)

SUPPLEMENTAL_ABSD_HIFC1_All

FOR UPDATE, INSERT, DELETE

Guard

TRIGGER_DISABLE (standard)

J06_AUTHOR and J06_LNCHDATE audit on ABSD_OVGE only — fires on all three event types
No validation, no cascade, no staging

SUPPLEMENTAL_ABSD_HIFL — Flag History (J06 only)

SUPPLEMENTAL_ABSD_HIFL_All

FOR UPDATE, INSERT, DELETE

Guard

TRIGGER_DISABLE (standard)

J06_AUTHOR and J06_LNCHDATE audit on ABSD_OVGE only — fires on all three event types
No validation, no cascade, no staging
Note: SUPPLEMENTAL_ABSD_OVGE also writes MMSI to this table (see SUPPLEMENTAL_ABSD_OVGE)

SUPPLEMENTAL_ABSD_HIMA — Manager Supplemental Fields

supp_hima_update

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

Annotation write for field H02_Cor grouping on SUPPLEMENTAL_ABSD_HIMA
J06 audit including J06_LNCHTIME

SUPPLEMENTAL_ABSD_HIMA_DELETE

FOR DELETE NO TRIGGER_DISABLE GUARD

J06 audit only — no guard, fires unconditionally on all deletes
Uses deleted pseudo-table (not inserted) for LRNO lookup

SUPPLEMENTAL_ABSD_HITL — Light Displacement Tonnes / CGT

SuppHITLUpdate

FOR INSERT, UPDATE NO TRIGGER_DISABLE GUARD

Guard

NONE — fires unconditionally on all INSERT/UPDATE

Cursor

Yes — per LRNO+SEQNO

⚠

SuppHITLUpdate has no TRIGGER_DISABLE guard — the entire trigger body runs unconditionally on every INSERT/UPDATE, regardless of the disable flag state.

LDT → SUPPLEMENTAL_ABSD_CADI Scrap Price Recalculation

When LDT (Light Displacement Tonnes) changes and there is a CADI record with a non-NULL AMNT (scrap price per LDT, in hundredths):
Reads LDT from SUPPLEMENTAL_ABSD_HITL SEQNO='00', reads AMNT from the most recent ABSD_CADI record (highest INNO)
Updates SUPPLEMENTAL_ABSD_CADI.TOTAL_SCRAP_PRICE = LDT × (AMNT / 100)
This recalculation triggers SuppCADIUpdate (which then cascades to ABSD_SALE)

LDT → ABSD_SHIP_SEARCH

For SEQNO='00' records only: updates ABSD_SHIP_SEARCH.LDT from the inserted value

Annotation

Annotation written for field grouping SUPPLEMENTAL_ABSD_HITL / CGT (Compensated Gross Tonnage) on every LRNO processed

SUPPLEMENTAL_ABSD_NCON — New Construction Supplemental

SuppNCONUPdate

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

J06 audit only — no validation, no cascade, no staging

SUPPLEMENTAL_ABSD_OVGE — MMSI, Yard Number, Fish Number

SuppOVGEUpdate

FOR INSERT, UPDATE DUAL GUARD

Guard

TRIGGER_DISABLE + TRIGGER_DISABLE_SUPPOVGE

Cursor

Yes — per LRNO (MMSI_cursor)

ℹ

Dual guard: Both TRIGGER_DISABLE and TRIGGER_DISABLE_SUPPOVGE must be empty for the trigger body to execute. Either guard table having a row prevents all processing.

MMSI Validation

When MMSI is supplied, calls spValidateMMSI(@lrno, ..., @mmsi, @Errcode OUTPUT, @Errmess OUTPUT)
If @Errcode=0: raises the error message and rolls back
The old trigger (now commented out) performed the MMSI range check inline; the current version delegates to spValidateMMSI

MMSI_EFD Date Validation

Validates MMSI_EFD via valStandard4_new (4-digit year format); prefixes error message with "MMSI EFD "

FISHNO20 → ABSD_OVGE.B10_FISHNO Cascade

When FISHNO20 changes: writes LEFT(LTRIM(RTRIM(FISHNO20)), 10) to ABSD_OVGE.B10_FISHNO
The supplemental table holds up to 20 characters; the base table field is limited to 10 characters

MMSI → ABSD_SHIP_SEARCH + SUPPLEMENTAL_ABSD_HIFL

Updates ABSD_SHIP_SEARCH.MMSI from the new value
Also updates SUPPLEMENTAL_ABSD_HIFL.MMSI for SEQNO='00' — keeps the flag history table's MMSI in sync (MW November 2017: Callsign and MMSI History task)

tblChanges Audit (MMSI and YARD_NO)

Uses spGetEnglishFieldName to get the human-readable field label
On INSERT (no row in deleted): OldValue = NULL
On UPDATE: OldValue = value from deleted pseudo-table

Staging

Inserts/replaces into SUPPLEMENTAL_ABSD_OVGE_UPDATES staging table

SUPPLEMENTAL_ABSD_OVTY — Ship Type (STAT5CODE / Hull Shape)

SUPP_ABSD_OVTY_Update

FOR UPDATE, INSERT

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per LRNO (INDEX_cursor)

STAT5CODE Foreign-Key Validation

STAT5CODE must exist in tblMasterShipTypeXref (case-insensitive: COLLATE Latin1_General_CI_AI)
If not found: "The code in Stat5Code does not match a record in tblMasterShipTypeXref.", rollback

HULL_SHAPE Foreign-Key Validation

HULL_SHAPE must exist in ABSD_CBUB1 with FLDI='11' (hull shape codebook bucket)
If not found: "The code in Hull_shape does not match a record in ABSD_CBUB1.", rollback

Passenger Vessel Berthed/Decked Consistency Rules

Passenger STAT5CODEs: A36A2PT (passenger), A37B2PS (RoPax), A37A2PC (cruise), A36A2PR (river), A36B2PL (launch), A32A2GF (ferry)
Rule 1: If DECKED=1 or BERTHED=1 but STAT5CODE is NOT a passenger type → error: "The Stat5Code entered isn't a passenger vessel Berthed or Decked shouldn't be chosen."
Rule 2: If STAT5CODE IS a passenger type but both DECKED=0 and BERTHED=0 → error: "Please select a combination if Berthed or Decked options for your passenger vessel."

STAT5CODE → ABSD_SHIP_SEARCH.STATDECODE (SEQ_NO='00' only)

Looks up tblStatCode5.Level5Decode for the new STAT5CODE
Writes LEFT(Level5Decode, 50) to ABSD_SHIP_SEARCH.STATDECODE

tblChanges Audit — Historical Awareness

If SEQ_NO='00': IsCurrent=1, Notes=NULL
If SEQ_NO≠'00' and IsNew=1 (insert): Notes='Made historical'
If SEQ_NO≠'00' and IsNew=0 (update): Notes='Historical amendment'
OldValue logic: new record + current SEQ → NULL; new record + historical SEQ → current STAT5CODE; update → previous STAT5CODE from deleted

SUPPLEMENTAL_ABSD_STDE — Number of Decks

SUPP_STDE_UPDATE

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

Required Field Validation

C05_NO_DECKS must have LEN ≥ 1 (cannot be empty/NULL)
Error: "Number of decks is a required field", rollback

Range-Capped Cascade to ABSD_STDE

If C05_NO_DECKS > 9: writes 0 to ABSD_STDE.C05_NO_DECKS (the base table field is a single digit; values above 9 overflow it)
If C05_NO_DECKS ≤ 9: writes actual value to ABSD_STDE.C05_NO_DECKS

SUPPLEMENTAL_ABSD_STSE — Yard Number / Standard Design

SuppSTSEUpdate

FOR INSERT, UPDATE

Guard

TRIGGER_DISABLE (standard)

Cursor

Yes — per LRNO (SUPPSTSE)

YARD_NO20 → ABSD_STSE.C01_YRDNO (12-char truncation)

When YARD_NO20 or SEQNO changes: writes RIGHT(LTRIM(RTRIM(YARD_NO20)), 12) to ABSD_STSE.C01_YRDNO for the matching LRNO+SEQNO
Takes the rightmost 12 characters (in case the 20-char value has significant trailing digits)

SEQNO='01' YARD_NO20 → SUPPLEMENTAL_ABSD_OVGE.YARD_NO20

The SEQNO='01' record is the primary/main builder record
When this record's YARD_NO20 changes, cascades to SUPPLEMENTAL_ABSD_OVGE.YARD_NO20 for the same LRNO (trimmed)
This keeps the vessel's "main yard number" visible from the general supplemental vessel record

STANDARDDESIGN → ABSD_SHIP_SEARCH

When STANDARDDESIGN changes: updates ABSD_SHIP_SEARCH.STANDARDDESIGN for the matching LRNO

⚠

The annotation lookup uses Tablename='ABSD_HIPP' and Fieldname='Related_LRNO' — this appears to be a copy-paste error; the annotation grouping points to a different table. The annotation fires but may write under the wrong grouping.

SUPPLEMENTAL_ABSD_STSE_DELETE

FOR DELETE NO TRIGGER_DISABLE GUARD

J06 audit only — no guard, fires unconditionally
Uses deleted pseudo-table for LRNO lookup

SUPPLEMENTAL_ABSD_STSE_COMPDATE — Completion Date

SUPP_ABSD_STSE_COMPDATE_Update

FOR UPDATE, INSERT

Guard

TRIGGER_DISABLE (standard)

COMP_DATE Validation

Validates via valStandard9new (9-digit date format YYYYMMDD); error prefixed with "Completion Date "

COMP_DATE + SEQNO='00' → ABSD_STSE Cascade with Status Branch

Only when COMP_DATE changes and SEQNO='00'
Branch 1 — Active vessel (ABSD_HIST SEQNO='00' status in P/O/U/E/F): Updates ABSD_STSE without a SEQNO filter (updates the current SEQNO='00' structural record)
Branch 2 — Non-active vessel: Updates ABSD_STSE where SEQNO='01' only (construction record)
Sets both C01_EFD = LEFT(COMP_DATE, 6) (YYYYMM) and C01_COMP_DT = COMP_DATE (YYYYMMDD)

Cross-Reference: SUPPLEMENTAL Trigger Summary

Trigger	Event	Guard	Key Business Rule	Key Downstream
`SuppCADIUpdate`	U/I	Standard	TOTAL_SCRAP_PRICE → ABSD_SALE (create/update/history); CALA audit	ABSD_SALE, tblFSWSalesPage, ABSD_CALA
`UpdateShortName`	U	None	SHORT_NAME_PCASE → ABSD_CBCOROOT.OUT_SHORT_NAME; Type T → SHIP_SEARCH.SHIPBUILDER	ABSD_CBCOROOT, ABSD_SHIP_SEARCH
`SUPP_ABSD_CBCY_UPD`	U/I	Standard	Sync SUPPLEMENTAL→ABSD_CBCY+Fairplay; MK01='C' sanctions management	ABSD_CBCY, tblFairplayCountryUniqueIMO, tblSanctionedCountry
`SUPP_ABSD_CBCY_DEL`	D	None	Delete open tblSanctionedCountry rows	tblSanctionedCountry
`SUPP_FUDI_UPDATE`	U/I	Standard	spValidateManbow_Len; cascade D22_MANBOW_LEN to ABSD_FUDI1	ABSD_FUDI1, J06, annotation
`SUPPLEMENTAL_ABSD_FUDI2_All`	U/I/D	Standard	J06 audit only	ABSD_OVGE J06
`supp_fuge_update`	U/I	Standard	Annotation on TANK_HEAT_EX; J06 with LNCHTIME	tblAnnotationLogGeneral, J06
`FUUN1_Supp_Upd`	U/I	Standard	D11_TEU_CAP cascade (0 if >99999); SHIP_SEARCH.TEU; annotation D11_TEU_CAP/TEU_14	ABSD_FUUN1, ABSD_SHIP_SEARCH, J06
`SUPPLEMENTAL_ABSD_HIFC1_All`	U/I/D	Standard	J06 audit only	ABSD_OVGE J06
`SUPPLEMENTAL_ABSD_HIFL_All`	U/I/D	Standard	J06 audit only	ABSD_OVGE J06
`supp_hima_update`	U/I	Standard	Annotation H02_Cor; J06 with LNCHTIME	tblAnnotationLogGeneral, J06
`SUPPLEMENTAL_ABSD_HIMA_DELETE`	D	None	J06 audit on delete	ABSD_OVGE J06
`SuppHITLUpdate`	U/I	None	LDT → SUPP_CADI scrap price recalc; SHIP_SEARCH.LDT; annotation CGT	SUPPLEMENTAL_ABSD_CADI, ABSD_SHIP_SEARCH, J06
`SuppNCONUPdate`	U/I	Standard	J06 audit only	ABSD_OVGE J06
`SuppOVGEUpdate`	U/I	TRIGGER_DISABLE + TRIGGER_DISABLE_SUPPOVGE	spValidateMMSI; valStandard4_new; FISHNO20→B10_FISHNO; MMSI→SHIP_SEARCH+HIFL; tblChanges MMSI/YARD_NO	ABSD_OVGE, ABSD_SHIP_SEARCH, SUPPLEMENTAL_ABSD_HIFL, SUPP_OVGE_UPDATES, tblChanges
`SUPP_ABSD_OVTY_Update`	U/I	Standard	STAT5CODE FK; HULL_SHAPE FK; passenger DECKED/BERTHED consistency; STAT5CODE→SHIP_SEARCH.STATDECODE; tblChanges historical-aware	ABSD_SHIP_SEARCH, tblChanges, J06
`SUPP_STDE_UPDATE`	U/I	Standard	NO_DECKS required; >9 → cascade 0; else cascade actual	ABSD_STDE.C05_NO_DECKS
`SuppSTSEUpdate`	U/I	Standard	YARD_NO20→ABSD_STSE (12-char); SEQNO='01' YARD_NO→SUPP_OVGE; STANDARDDESIGN→SHIP_SEARCH	ABSD_STSE, SUPPLEMENTAL_ABSD_OVGE, ABSD_SHIP_SEARCH
`SUPPLEMENTAL_ABSD_STSE_DELETE`	D	None	J06 audit on delete	ABSD_OVGE J06
`SUPP_ABSD_STSE_COMPDATE_Update`	U/I	Standard	valStandard9new; COMP_DATE→ABSD_STSE C01_EFD+C01_COMP_DT (active vs construction branch)	ABSD_STSE