SUPPLEMENTAL Tables

Extended schema overflow tables beyond the core ABSD_ schema — with full trigger, validation, and cascade logic derived from actual SQL source

Tables

Triggers

SHIP_SEARCH Fields

Primary Annotations

Sanctions Tables

Architecture Overview

SUPPLEMENTAL tables exist to hold fields that could not fit into the fixed-width ABSD_ schema (which was historically bounded by a legacy system) or that represent extended/overflow data logically belonging to an ABSD_ entity. Every SUPPLEMENTAL table has a 1:1 or 1:N relationship with its corresponding ABSD_ base table.

All SUPPLEMENTAL triggers serve the same core architectural goals:

Keep the ABSD_ base table synchronised when the SUPPLEMENTAL row acts as an overflow buffer
Update ABSD_OVGE.J06_AUTHOR and J06_LNCHDATE (universal last-modified audit)
Fire primary annotations into tblChanges for field-level change history
Fire secondary annotations into tblAnnotationLogGeneral for grouping-level tracking
Update ABSD_SHIP_SEARCH for any denormalised display fields

Application / Editor

→

SUPPLEMENTAL_ABSD_*
INSERT/UPDATE

→

ABSD_ base table
sync via trigger

→

ABSD_SHIP_SEARCH
tblChanges
J06 audit

Trigger Guard Tables

All SUPPLEMENTAL triggers (and all ABSD_ triggers) check one or more guard tables before executing. Writing a row to a guard table effectively disables the corresponding trigger for the duration of a batch operation.

Guard Table	Scope	Purpose
`TRIGGER_DISABLE`	Global	Disables ALL triggers on all tables. Used during bulk data loads and system migrations.
`TRIGGER_DISABLE_SUPPOVGE`	SUPPLEMENTAL_ABSD_OVGE only	Prevents recursion when SUPPLEMENTAL_ABSD_OVGE is updated by another trigger (e.g. during HIFL flag change propagation).
`TRIGGER_DISABLE_OVGE`	ABSD_OVGE only	Disables the ABSD_OVGE trigger during batch operations that would otherwise loop.
`TRIGGER_DISABLE_OVNA`	ABSD_OVNA only	Disables the ABSD_OVNA trigger (name history) during controlled resequencing.

⚠️

Guard table usage: Guard tables must always be cleaned up after batch operations. A row left in TRIGGER_DISABLE will silently suppress all trigger logic — J06 audit, annotations, and SHIP_SEARCH updates will all fail to fire.

Common Trigger Pattern

All SUPPLEMENTAL triggers follow this sequence. Individual tables deviate only by adding or omitting specific steps:

Guard check: IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE) — exits silently if any row present. Some also check table-specific guards (e.g. TRIGGER_DISABLE_SUPPOVGE).

Field validation: Calls val* stored procedures (valStandard4_new for dates, spValidateMMSI for MMSI numbers, etc.). On failure: RAISERROR + ROLLBACK TRANSACTION + RETURN.

ABSD_ base table sync: Mirrors inserted value(s) into the corresponding ABSD_ column. Some apply truncation rules (RIGHT(LTRIM(RTRIM(val)),N)) or overflow clamping (value > limit → write 0).

J06 audit update: UPDATE ABSD_OVGE SET J06_AUTHOR = [3-char user], J06_LNCHDATE = [YYMMDD] for each affected LRNO. This fires on every trigger, including DELETE triggers.

Staging table update: For tables with an _UPDATES counterpart, deletes the existing row and inserts the new inserted row into the staging table.

Primary annotation (tblChanges): Calls spGetUserSettings + spGetEnglishFieldName, then inserts into tblChanges with OldValue (from deleted), NewValue (from inserted), and optional Notes ('Made historical' / 'Historical amendment' for non-00 SEQNO).

Secondary annotation (tblAnnotationLogGeneral): Looks up grouping config from indAnnotation_SecondaryGroupings then inserts one row per LRNO.

ABSD_SHIP_SEARCH update: For any field that is denormalised into the search cache (VESSELNAME, FLAG, MMSI, TEU, LDT, etc.), updates the corresponding column, gated on SEQNO='00' where applicable.

Table Index

Table	PK / Key	Domain	Trigger	SHIP_SEARCH Fields
`SUPPLEMENTAL_ABSD_OVGE`	LRNO	Vessel General Extended	SuppOVGEUpdate (INS/UPD)	MMSI
`SUPPLEMENTAL_ABSD_OVGE_UPDATES`	LRNO	Staging copy of OVGE	None (maintained by SuppOVGEUpdate)	—
`SUPPLEMENTAL_ABSD_OVTY`	LRNO+SEQ_NO	Vessel Type Extended	SUPP_ABSD_OVTY_Update (INS/UPD)	STATDECODE
`SUPPLEMENTAL_ABSD_NCON`	LRNO	Newbuild Commercial	SuppNCONUPdate (INS/UPD)	—
`SUPPLEMENTAL_ABSD_HIFL`	LRNO+SEQNO	Flag History Extended	SUPPLEMENTAL_ABSD_HIFL_All (UPD/INS/DEL)	—
`SUPPLEMENTAL_ABSD_HITL`	LRNO+SEQNO	Tonnage Extended	SuppHITLUpdate (INS/UPD cursor)	LDT
`SUPPLEMENTAL_ABSD_HIMA`	LRNO+SEQNO	Manager Extended	supp_hima_update (INS/UPD) + DELETE trigger	—
`SUPPLEMENTAL_ABSD_HIFC1`	LRNO+SEQNO	Class Notation Extended	SUPPLEMENTAL_ABSD_HIFC1_All (UPD/INS/DEL)	—
`SUPPLEMENTAL_ABSD_STSE`	LRNO+SEQNO	Ship General Extended	SuppSTSEUpdate (INS/UPD cursor)	STANDARDDESIGN
`SUPPLEMENTAL_ABSD_STSE_COMPDATE`	LRNO+SEQNO	Completion Date	SUPP_ABSD_STSE_COMPDATE_Update (INS/UPD)	—
`SUPPLEMENTAL_ABSD_STDE`	LRNO	Structural Decks	SUPP_STDE_UPDATE (INS/UPD)	—
`SUPPLEMENTAL_ABSD_FUUN1`	LRNO	Container Capacity	FUUN1_Supp_Upd (INS/UPD cursor)	TEU
`SUPPLEMENTAL_ABSD_FUGE`	LRNO	Additional Equipment	supp_fuge_update (INS/UPD)	—
`SUPPLEMENTAL_ABSD_FUDI1`	LRNO	Manbow Length	SUPP_FUDI_UPDATE (INS/UPD cursor)	—
`SUPPLEMENTAL_ABSD_FUDI2`	LRNO+SEQ_NO	Cargo Pump	SUPPLEMENTAL_ABSD_FUDI2_All (UPD/INS/DEL)	—
`SUPPLEMENTAL_ABSD_CADI`	LRNO+INNO	Scrapping Price	SuppCADIUpdate (INS/UPD)	—
`SUPPLEMENTAL_ABSD_CBCOROOT`	5-part PK	Shipbuilder Short Name	UpdateShortName (UPD)	SHIPBUILDER
`SUPPLEMENTAL_ABSD_CBCY`	KEY(3)	Country Codebook + Sanctions	SUPP_ABSD_CBCY_UPD (INS/UPD) + DEL trigger	—
`SUPPLEMENTAL_ABSD_OWGE`	OWCODE	Owner General Extended	None	—

SUPPLEMENTAL_ABSD_OVGE

Vessel General Extended SHIP_SEARCH: MMSI Primary: MMSI, YARD_NO

Extends ABSD_OVGE with fields that overflow the main vessel-general record. Holds MMSI, a 20-char yard number extension, fishing number extension, pennant number, and photo flag. Has its own staging table SUPPLEMENTAL_ABSD_OVGE_UPDATES.

Schema

Column	Type	Notes
`LRNO`	varchar(7) PK	LR Number — primary key
`MMSI`	varchar(9) NULL	Maritime Mobile Service Identity — validated via `spValidateMMSI`; mirrored to SHIP_SEARCH and SUPPLEMENTAL_ABSD_HIFL SEQNO='00'
`MMSI_EFD`	varchar(6) NULL	MMSI Effective Date (YYMMDD) — validated via `valStandard4_new`; also propagated to ABSD_HIFL.B01_EFD family by upstream HIFL trigger
`YARD_NO`	varchar(12) NULL	Yard number — annotated in tblChanges (primary)
`PHOTO`	bit NULL	Photo available flag
`PENNANT_NO`	varchar(10) NULL	Naval pennant number
`FISHNO20`	varchar(20) NULL	Fishing number (20-char); trigger truncates LEFT 10 chars into `ABSD_OVGE.B10_FISHNO`
`YARD_NO20`	varchar(20) NULL	Yard number (20-char) — synced with SUPPLEMENTAL_ABSD_STSE.YARD_NO20 SEQNO='01' record

ℹ️

Index: Non-clustered index SUPPLEMENTAL_ABSD_OVGEX2 on MMSI ASC — enables fast MMSI lookup across the fleet.

Trigger: SuppOVGEUpdate (FOR INSERT, UPDATE)

Also checks TRIGGER_DISABLE_SUPPOVGE (second guard) to prevent recursion from upstream cascades.

MMSI validation: Cursor per LRNO. Calls spValidateMMSI(@lrno, @seqno, null, …, @mmsi, @Errcode OUTPUT, @Errmess OUTPUT). If Errcode=0 → RAISERROR + ROLLBACK.

MMSI_EFD validation: Calls valStandard4_new. On failure: RAISERROR + ROLLBACK.

FISHNO20 sync: UPDATE ABSD_OVGE SET B10_FISHNO = LEFT(LTRIM(RTRIM(FISHNO20)), 10) — truncates to base field width.

J06 audit: Updates J06_AUTHOR (3-char from Windows login) and J06_LNCHDATE (YYMMDD) on ABSD_OVGE.

Staging: DELETE + INSERT into SUPPLEMENTAL_ABSD_OVGE_UPDATES.

Primary annotations: tblChanges entries for MMSI (with old/new values) and YARD_NO.

Secondary annotation: Looks up 'Yard_no' grouping from indAnnotation_SecondaryGroupings and inserts into tblAnnotationLogGeneral.

SHIP_SEARCH.MMSI: UPDATE ABSD_SHIP_SEARCH SET MMSI = INSERTED.MMSI.

SUPPLEMENTAL_ABSD_HIFL.MMSI sync: Also updates SUPPLEMENTAL_ABSD_HIFL.MMSI for SEQNO='00' — keeps MMSI history current record in sync (added Nov 2017, Callsign and MMSI History Task).

SUPPLEMENTAL_ABSD_OVGE_UPDATES

Staging / Buffer No Trigger

Staging buffer maintaining the last-known state of SUPPLEMENTAL_ABSD_OVGE. Updated exclusively by the SuppOVGEUpdate trigger — never written to directly by the application. Has exactly the same columns as SUPPLEMENTAL_ABSD_OVGE with a single-column PK on LRNO.

Used by downstream processes to detect net changes to MMSI, YARD_NO, and FISHNO20 without reading the full change history.

SUPPLEMENTAL_ABSD_OVTY

Vessel Type Extended SHIP_SEARCH: STATDECODE Primary: STAT5CODE

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQ_NO`	nvarchar(2) PK	Sequence number ('00' = current)
`HULL_SHAPE`	nvarchar(2) NULL	Hull shape code — validated against ABSD_CBUB1 FLDI='11'
`STAT5CODE`	nvarchar(7) NULL	5-digit ship type code — validated against tblMasterShipTypeXref; drives SHIP_SEARCH.STATDECODE
`BERTHED`	bit NULL	Passenger berthed indicator — only allowed for specific STAT5CODEs (passenger vessel types)
`DECKED`	bit NULL	Passenger decked indicator — same constraint as BERTHED

Trigger: SUPP_ABSD_OVTY_Update (FOR INSERT, UPDATE)

STAT5CODE validation: Checks tblMasterShipTypeXref — rejects codes not in the master type reference table.

HULL_SHAPE validation: Checks ABSD_CBUB1 WHERE FLDI='11' — hull shape must exist in codebook table 11.

Passenger constraint: BERTHED and DECKED may only be set for vessels whose STAT5CODE maps to a passenger category. Non-passenger ships → RAISERROR + ROLLBACK.

J06 audit: Updates ABSD_OVGE J06 fields.

Primary annotation for STAT5CODE: tblChanges entry with historical notes logic (non-00 SEQNO records get 'Made historical' or 'Historical amendment').

SHIP_SEARCH.STATDECODE (SEQNO='00' only): Looks up the human-readable ship type description via tblStatCode5 and updates ABSD_SHIP_SEARCH.STATDECODE.

SUPPLEMENTAL_ABSD_NCON

Newbuild Commercial Minimal Trigger

Holds commercial newbuilding data supplementing ABSD_NCON. The RELEASEDATE field is set automatically by the ABSD_HIST trigger when a vessel transitions from a newbuild status (P/O/U/E/F) to in-service ('S'). NBEmailIndicator is managed by both this trigger and the ABSD_HIST trigger.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`RELEASEDATE`	nvarchar/date	Auto-set by ABSD_HIST trigger on delivery to service (dd:mm:yy format)
`NBPriceCurrencyCode`	nvarchar(3)	ISO currency code for newbuild price
`NBPrice`	bigint DEFAULT 0	Newbuild contract price in native currency
`NBPriceUSDEquivalent`	int DEFAULT 0	USD equivalent of newbuild price
`NBPriceEuroEquivalent`	int DEFAULT 0	Euro equivalent of newbuild price
`NewconSubStatus`	nvarchar(20)	Newbuilding sub-status
`NewconSubStatusNote`	nvarchar(100)	Narrative note on sub-status
`NewconSubStatusNumber`	int	Numeric code for sub-status
`NBEmailIndicator`	bit DEFAULT 0	Controls newbuilding email notifications; set/cleared by ABSD_HIST trigger on status transitions

ℹ️

NBEmailIndicator lifecycle: Set to 1 by ABSD_HIST when status enters P/O/U/F/E or X (cancelled). Set to 0 when leaving those statuses. ABSD_HIST also upserts this table's RELEASEDATE on first delivery (previous status in P/O/U/E/F → new status 'S').

Trigger: SuppNCONUPdate (FOR INSERT, UPDATE)

Minimal trigger — only fires J06 audit update on ABSD_OVGE. No validation, no SHIP_SEARCH update, no annotations.

SUPPLEMENTAL_ABSD_HIFL

Flag History Extended Minimal Trigger

Extends the flag history table ABSD_HIFL with additional fields — specifically CALLSIGN and MMSI — providing per-sequence-number history of these identifiers. The SEQNO='00' row is kept in sync by SuppOVGEUpdate (MMSI) and upstream triggers (CALLSIGN).

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence ('00'=current, '01'=previous etc.)
`CALLSIGN`	nvarchar(13) NULL	Radio callsign for this flag period — maintained by ABSD_OVGE trigger cascade
`MMSI`	nvarchar(9) NULL	MMSI for this period — SEQNO='00' kept in sync by SuppOVGEUpdate

Trigger: SUPPLEMENTAL_ABSD_HIFL_All (FOR UPDATE, INSERT, DELETE)

Minimal trigger — fires J06 audit update on ABSD_OVGE for all LRNOs in inserted (note: DELETE trigger uses inserted pseudo-table which is empty; J06 update is effectively a no-op on DELETE via this trigger).

SUPPLEMENTAL_ABSD_HITL

Tonnage Extended SHIP_SEARCH: LDT Scrapping Price Calc

Extends ABSD_HITL (hull/tonnage history) with additional tonnage metrics including CGT, LDT, and steel counts. The LDT field is particularly important: it drives the SHIP_SEARCH denormalisation and the automatic scrapping price calculation in SUPPLEMENTAL_ABSD_CADI.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence number
`CGT`	int NULL	Compensated Gross Tonnage — secondary annotation fired on change
`LDT`	int NULL	Lightweight Displacement Tonnage — drives scrap price and SHIP_SEARCH.LDT
`PCNT`	int NULL	Passenger count
`SCNT`	int NULL	Secondary count (context-specific)

Trigger: SuppHITLUpdate (FOR INSERT, UPDATE) — Cursor per LRNO+SEQNO

LDT → Scrap price: If LDT updated AND SUPPLEMENTAL_ABSD_CADI has an AMNT value for this vessel: TOTAL_SCRAP_PRICE = LDT × (AMNT / 100). Updates SUPPLEMENTAL_ABSD_CADI.

LDT → SHIP_SEARCH (SEQNO='00' only): UPDATE ABSD_SHIP_SEARCH SET LDT = inserted.LDT.

J06 audit: Updates ABSD_OVGE J06 fields.

Secondary annotation for CGT: Fires CGT grouping annotation in tblAnnotationLogGeneral.

SUPPLEMENTAL_ABSD_HIMA

Manager Extended

Extends ABSD_HIMA (manager history) with two additional country codes: country of registration and country of commercial management.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence number
`H02_COR`	nvarchar(3) NULL	Country of Registration — 3-char code
`H02_COC`	nvarchar(3) NULL	Country of Commercial Management — 3-char code

Trigger: supp_hima_update (FOR INSERT, UPDATE)

Secondary annotation: Fires annotation in tblAnnotationLogGeneral (grouping for H02_COR/H02_COC fields).

J06 audit with time: Updates J06_AUTHOR, J06_LNCHDATE, and also J06_LNCHTIME on ABSD_OVGE (one of the few triggers that also records the time).

Trigger: SUPPLEMENTAL_ABSD_HIMA_DELETE (FOR DELETE)

J06 audit only (author + date).

SUPPLEMENTAL_ABSD_HIFC1

Class Notation Extended Minimal Trigger

Extends ABSD_HIFC1 (class history) with extended hull notation and a class identifier for external system cross-referencing.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence number
`FC_HULL_NOTATION`	nvarchar(126) NULL	Full-text hull class notation (extended beyond 8-char ABSD limit)
`FC_CLASS_ID`	nvarchar(20) NULL	Class society's own identifier for this notation record

Trigger: SUPPLEMENTAL_ABSD_HIFC1_All (FOR UPDATE, INSERT, DELETE)

Minimal trigger — J06 audit only.

SUPPLEMENTAL_ABSD_STSE

Ship General Extended SHIP_SEARCH: STANDARDDESIGN ABSD_STSE sync: C01_YRDNO

Extends ABSD_STSE (hull/ship general) with a longer yard number, a standard design identifier, and the related (sister/parent) vessel LRNO.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence number ('01'=current hull record)
`RELATED_LRNO`	nvarchar(7) NULL	Sister/parent vessel LRNO — secondary annotation via 'ABSD_HIPP'/'Related_LRNO' grouping
`STANDARDDESIGN`	nvarchar(15) NULL	Standard design identifier — mirrored to SHIP_SEARCH.STANDARDDESIGN
`YARD_NO20`	nvarchar(20) NULL	Extended yard number (20 chars); truncated to 12 when synced to ABSD_STSE.C01_YRDNO

Trigger: SuppSTSEUpdate (FOR INSERT, UPDATE) — Cursor per LRNO+SEQNO

YARD_NO20 → ABSD_STSE.C01_YRDNO: UPDATE ABSD_STSE SET C01_YRDNO = RIGHT(LTRIM(RTRIM(YARD_NO20)), 12) — right-aligns and truncates to the 12-char base field.

YARD_NO20 → SUPPLEMENTAL_ABSD_OVGE.YARD_NO20 (SEQNO='01' only): The SEQNO='01' hull record's YARD_NO20 is propagated back to the vessel's SUPPLEMENTAL_ABSD_OVGE row.

STANDARDDESIGN → SHIP_SEARCH.STANDARDDESIGN: UPDATE ABSD_SHIP_SEARCH SET STANDARDDESIGN = inserted.STANDARDDESIGN.

J06 audit.

Secondary annotation: RELATED_LRNO grouping fires into tblAnnotationLogGeneral.

SUPPLEMENTAL_ABSD_STSE_COMPDATE

Completion Date ABSD_STSE sync: C01_COMP_DT

Provides a per-sequence completion date for hull records, allowing precise delivery/completion tracking beyond the 6-char YYMMDD date stored in ABSD_STSE.C01_COMP_DT. The full 8-char YYYYMMDD date here supersedes the base-table date when propagated.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQNO`	nvarchar(2) PK	Sequence number
`EFD`	nvarchar(8) NULL	Effective date (YYYYMMDD)
`COMP_DATE`	nvarchar(8) NULL	Completion date — validated via `valStandard9new` (8-char date)
`EST_IND`	varchar(2) NULL	Estimated indicator (E=estimated, confirmed otherwise)

Trigger: SUPP_ABSD_STSE_COMPDATE_Update (FOR UPDATE, INSERT)

COMP_DATE validation: Calls valStandard9new (8-char date validator). On failure: RAISERROR + ROLLBACK.

ABSD_STSE sync (SEQNO='00' only):

If ABSD_HIST.A02_STS IN ('P','O','U','E','F') (newbuild status): updates the SEQNO '00' hull record — C01_EFD = LEFT(COMP_DATE, 6), C01_COMP_DT = COMP_DATE.
Otherwise: updates the SEQNO '01' hull record (in-service vessel).

Secondary annotation: COMP_DATE grouping fires into tblAnnotationLogGeneral.

SUPPLEMENTAL_ABSD_STDE

Structural Decks ABSD_STDE sync: C05_NO_DECKS

Schema

Column	Type	Notes
`LRNO`	varchar(7) PK	Vessel LRNO
`C05_NO_DECKS`	varchar(2) NOT NULL	Number of decks — required field; values >9 clamp to 0 in ABSD_STDE

Trigger: SUPP_STDE_UPDATE (FOR INSERT, UPDATE)

Required field check: IF LEN(C05_NO_DECKS) < 1 → RAISERROR + ROLLBACK.

J06 audit.

ABSD_STDE sync with clamping: If value > 9 → writes 0 to ABSD_STDE.C05_NO_DECKS; else mirrors the value.

Secondary annotation: C05_NO_DECKS grouping.

SUPPLEMENTAL_ABSD_FUUN1

Container Capacity SHIP_SEARCH: TEU ABSD_FUUN1 sync: D11_TEU_CAP

Extends ABSD_FUUN1 (container ship data) with the 14-tonne TEU figure alongside the standard TEU capacity. The trigger caps D11_TEU_CAP at 99,999 when syncing back to the base table.

Schema

Column	Type	Notes
`LRNO`	varchar(7) PK	Vessel LRNO
`D11_TEU_CAP`	int NOT NULL	Total TEU capacity; values >99999 → ABSD_FUUN1 receives 0 (overflow guard)
`TEU_14`	int NOT NULL	TEU capacity at 14-tonne homogeneous stow

Trigger: FUUN1_Supp_Upd (FOR INSERT, UPDATE) — Cursor per LRNO

J06 audit.

D11_TEU_CAP → ABSD_FUUN1 with clamping: If D11_TEU_CAP > 99,999 → writes 0; else mirrors value.

SHIP_SEARCH.TEU: UPDATE ABSD_SHIP_SEARCH SET TEU = inserted.D11_TEU_CAP.

Secondary annotation: D11_TEU_CAP and TEU_14 grouping annotations.

SUPPLEMENTAL_ABSD_FUGE

Additional Equipment

Provides extended equipment and crew data beyond ABSD_FUGE. Includes tank heating, maximum lift, berth capacity, and minimum/actual crew complement fields.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`TANK_HEAT_EX`	bit NULL	Tank heat exchanger fitted — secondary annotation on change
`SPECIALIST_CARGO_TEXT`	nvarchar(255) NULL	Free-text description of specialist cargo capability
`MAXIMUM_LIFT`	decimal(7,2) NULL	Maximum single-lift capacity (tonnes)
`Berth_capacity`	int NULL	Total passenger berth capacity
`MIN_REQUIRED_OFFICERS`	int NULL	Minimum statutory officer complement
`ACTUAL_OFFICERS`	int NULL	Actual officers on board
`MIN_RATINGS`	int NULL	Minimum statutory ratings complement
`ACTUAL_RATINGS`	int NULL	Actual ratings on board

Trigger: supp_fuge_update (FOR INSERT, UPDATE)

Secondary annotation for TANK_HEAT_EX: Fires tblAnnotationLogGeneral entry when TANK_HEAT_EX changes.

J06 audit with time: Updates J06_AUTHOR, J06_LNCHDATE, and J06_LNCHTIME (one of the few triggers that records time).

SUPPLEMENTAL_ABSD_FUDI1

Manbow Length ABSD_FUDI1 sync: D22_MANBOW_LEN

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`D22_MANBOW_LEN`	decimal(6,3) NULL	Manoeuvring bow length — validated via `spValidateManbow_Len`

Trigger: SUPP_FUDI_UPDATE (FOR INSERT, UPDATE) — Cursor per LRNO

D22_MANBOW_LEN validation: Calls spValidateManbow_Len — checks decimal range and format.

ABSD_FUDI1 sync: UPDATE ABSD_FUDI1 SET D22_MANBOW_LEN = inserted.D22_MANBOW_LEN.

J06 audit.

Secondary annotation.

SUPPLEMENTAL_ABSD_FUDI2

Cargo Pump Extended Minimal Trigger

Extends ABSD_FUDI2 (diesel/pump data) with cargo pump classification fields.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`SEQ_NO`	nvarchar(1) PK	Sequence number
`Cargo_Pump_Power_source`	nvarchar(1) NULL	Single-char pump power source code
`Cargo_Pump_Type`	nvarchar(1) NULL	Single-char pump type code

Trigger: SUPPLEMENTAL_ABSD_FUDI2_All (FOR UPDATE, INSERT, DELETE)

Minimal trigger — J06 audit only (set-based, no cursor).

SUPPLEMENTAL_ABSD_CADI

Scrapping Price ABSD_SALE management

Holds the computed total scrap price for a disposal (ABSD_CADI) record. The value is computed from LDT (from SUPPLEMENTAL_ABSD_HITL) × the per-unit scrap price (AMNT from ABSD_CADI). Has a cascading foreign key to ABSD_CADI — a deleted disposal record also deletes this row.

Schema

Column	Type	Notes
`LRNO`	nvarchar(7) PK	Vessel LRNO
`INNO`	nvarchar PK	Inspection/incident number — FK → ABSD_CADI(LRNO, INNO) CASCADE DELETE
`TOTAL_SCRAP_PRICE`	int NULL	Computed total scrap price: LDT × (AMNT/100)

ℹ️

Price calculation triangle: Three triggers interlock to maintain scrap price: (1) When SUPPLEMENTAL_ABSD_HITL.LDT changes → recomputes TOTAL_SCRAP_PRICE; (2) When ABSD_CADI.AMNT changes → recomputes TOTAL_SCRAP_PRICE; (3) When SUPPLEMENTAL_ABSD_CADI.TOTAL_SCRAP_PRICE changes → manages ABSD_SALE records via spCreateNewSaleWithPrice / spMakeSALEHistoricalWithPrice.

Trigger: SuppCADIUpdate (FOR INSERT, UPDATE)

Scrap price → ABSD_SALE management: Based on new TOTAL_SCRAP_PRICE, calls spCreateNewSaleWithPrice to create a sale record or spMakeSALEHistoricalWithPrice to archive an old one.

ABSD_CALA audit: Updates ABSD_CALA.EDITOR and EDIT_DATE for the matching LRNO+INNO — the casualty log editor tracking.

SUPPLEMENTAL_ABSD_CBCOROOT

Codebook: Shipbuilder SHIP_SEARCH: SHIPBUILDER ABSD_CBCOROOT sync: OUT_SHORT_NAME

Provides a proper-cased short name for entries in the ABSD_CBCOROOT shipbuilder/owner codebook. The trigger propagates the value back to ABSD_CBCOROOT.OUT_SHORT_NAME as upper-case and, for type 'T' entries (trading companies/yards), also updates ABSD_SHIP_SEARCH.SHIPBUILDER.

Schema

Column	Type	Notes
`OUT_TYPE`	nvarchar PK (part 1)	Type code: 'T'=trading/yard, others for other entity types
`OUT_CODE_FLAG`	nvarchar PK (part 2)	Flag country part of the 4-part code
`OUT_CODE_BLDR`	nvarchar PK (part 3)	Builder code part
`OUT_CODE_YARD`	nvarchar PK (part 4)	Yard code part
`OUT_CODE_CHK`	nvarchar PK (part 5)	Check digit
`SHORT_NAME_PCASE`	nvarchar(40) NULL	Proper-cased short name — stored as proper-case here, synced as UPPER() to ABSD_CBCOROOT

Trigger: UpdateShortName (FOR UPDATE)

ABSD_CBCOROOT.OUT_SHORT_NAME sync: UPDATE ABSD_CBCOROOT SET OUT_SHORT_NAME = UPPER(inserted.SHORT_NAME_PCASE).

SHIP_SEARCH.SHIPBUILDER (OUT_TYPE='T' only): UPDATE ABSD_SHIP_SEARCH SET SHIPBUILDER = UPPER(SHORT_NAME_PCASE) for all vessels with this builder code.

SUPPLEMENTAL_ABSD_CBCY

Codebook: Country Sanctions Management 3-table sync: ABSD_CBCY + tblFairplayCountryUniqueIMO + tblSanctionedCountry

The country codebook extension, providing a richer and more current country record than the legacy ABSD_CBCY table. Changes here are the authoritative source and propagate to three downstream tables. The sanctions management logic (via the MK01='C' flag) is the most complex part of this trigger.

Schema (selected columns)

Column	Type	Notes
`KEY`	nvarchar(3) PK	3-character country code
`FDATE` / `TDATE`	nvarchar(6)	From/to dates (YYMMDD) — TDATE='999912' means current
`PRNT`	nvarchar(3)	Parent country code
`MK01`–`MK20`	nvarchar(1)	Flag codes — MK01='C' means SANCTIONED
`ISO2` / `ISO3` / `ISONo`	nvarchar	ISO 3166-1 alpha-2, alpha-3, and numeric codes
`Country_Name`	nvarchar	Full country name
`Telephone_Dial_Code`	nvarchar	International dialling prefix
`Area`	nvarchar	Geographic area/region classification
`DefaultPortRegistry`	nvarchar	Default port of registry for vessels flagged here
`TROC`	nvarchar	Territory of registration code
`RBS1` / `RBS2`	nvarchar	Register book series codes

Trigger: SUPP_ABSD_CBCY_UPD (FOR INSERT, UPDATE) — Cursor per KEY

ABSD_CBCY sync: Updates or inserts into ABSD_CBCY with the FDATE, TDATE, PRNT, POSN, MK01–MK20, and other fields.

tblFairplayCountryUniqueIMO sync: Updates country metadata used by the Fairplay IMO system.

Sanctions history management (MK01 changes):

If MK01 changes to 'C' (country becomes sanctioned): inserts an open sanctions record into tblSanctionedCountry with StartDate=today, EndDate=NULL.
If MK01 changes from 'C' (sanctions lifted): updates the open record in tblSanctionedCountry setting EndDate=today.
If MK01 is set to 'C' on an INSERT (new sanctioned country): inserts open record.

Trigger: SUPP_ABSD_CBCY_DEL (FOR DELETE)

Sanctions cleanup: Deletes any open (EndDate IS NULL) sanctions records from tblSanctionedCountry for the deleted country key.

⚠️

Sanctions audit trail: tblSanctionedCountry maintains a full history of sanctions periods per country. The open record (EndDate=NULL) represents the current sanctioned status. Deleting a SUPPLEMENTAL_ABSD_CBCY row purges the open record — historical closed records (with EndDate) are preserved.

SUPPLEMENTAL_ABSD_OWGE

Owner General Extended No Trigger

Simple owner-level extension. Keyed on OWCODE (not LRNO) — this is the only SUPPLEMENTAL table that operates at the owner level rather than the vessel level. Contains only a mail opt-out flag. No trigger is defined.

Schema

Column	Type	Notes
`OWCODE`	nvarchar(7) PK	Owner company code
`MAIL_OPT_OUT`	bit NULL	If 1, owner has opted out of marketing mailings

Cross-Table Relationship Map

The following diagram shows how SUPPLEMENTAL triggers cascade into each other and the major downstream tables:

SUPPLEMENTAL_ABSD_OVGE
  ├─ validates MMSI → spValidateMMSI
  ├─ J06 → ABSD_OVGE
  ├─ FISHNO20 → ABSD_OVGE.B10_FISHNO (trunc 10)
  ├─ MMSI → ABSD_SHIP_SEARCH.MMSI
  ├─ MMSI → SUPPLEMENTAL_ABSD_HIFL.MMSI (SEQNO='00')
  └─ staging → SUPPLEMENTAL_ABSD_OVGE_UPDATES

SUPPLEMENTAL_ABSD_HITL
  ├─ LDT × AMNT/100 → SUPPLEMENTAL_ABSD_CADI.TOTAL_SCRAP_PRICE
  └─ LDT (SEQNO='00') → ABSD_SHIP_SEARCH.LDT

SUPPLEMENTAL_ABSD_CADI
  └─ TOTAL_SCRAP_PRICE → spCreateNewSaleWithPrice / spMakeSALEHistoricalWithPrice → ABSD_SALE

SUPPLEMENTAL_ABSD_STSE
  ├─ YARD_NO20 → ABSD_STSE.C01_YRDNO (trunc RIGHT 12)
  ├─ YARD_NO20 (SEQNO='01') → SUPPLEMENTAL_ABSD_OVGE.YARD_NO20
  └─ STANDARDDESIGN → ABSD_SHIP_SEARCH.STANDARDDESIGN

SUPPLEMENTAL_ABSD_STSE_COMPDATE
  └─ COMP_DATE → ABSD_STSE.C01_EFD + C01_COMP_DT
                  (conditional: SEQNO='00' if newbuild, '01' if in-service)

SUPPLEMENTAL_ABSD_CBCY
  ├─ KEY → ABSD_CBCY (all fields sync)
  ├─ KEY → tblFairplayCountryUniqueIMO
  └─ MK01='C' changes → tblSanctionedCountry (open/close sanctions periods)

SUPPLEMENTAL_ABSD_CBCOROOT
  ├─ SHORT_NAME_PCASE → ABSD_CBCOROOT.OUT_SHORT_NAME (UPPER)
  └─ (OUT_TYPE='T') → ABSD_SHIP_SEARCH.SHIPBUILDER

SUPPLEMENTAL_ABSD_FUUN1
  ├─ D11_TEU_CAP → ABSD_FUUN1.D11_TEU_CAP (cap 99999)
  └─ D11_TEU_CAP → ABSD_SHIP_SEARCH.TEU

SUPPLEMENTAL_ABSD_OVTY
  └─ STAT5CODE (SEQNO='00') → tblStatCode5 → ABSD_SHIP_SEARCH.STATDECODE