10g — Structure, Registration & Survey Triggers

HI* Physical Dimension Triggers

ABSD_HIBR_Update

Table: ABSD_HIBR FOR UPDATE, INSERT Cursor: LRNO+SEQNO

Breadth record validation. Cursor redesign ensures multi-row batch safety. Cross-validates breadth against depth and draught to enforce physical plausibility.

Validation Rules

Cursor iterates over (LRNO, SEQNO) pairs from inserted
C09_MLD (moulded breadth) — when > 0, must be ≥ ABSD_HIDE.C10_MLD (moulded depth) for same LRNO+SEQNO
C09_EX (extreme breadth) — when > 0, must be ≥ C09_MLD
Cross-check: C09_MLD × ABSD_HIDR.C07_DL × ABSD_HILE.C02_LOA × 1.025 must be ≥ ABSD_STGE.C08_DSPLCMNT (displacement sanity)
valStandard4_new applied to C09_EFD
valStandard8 applied to C09_SRCE
valStandard1 applied to C09_VER

DML Actions

J06 audit written to ABSD_OVGE (author + date)
Staging: ABSD_HIBR_UPDATES refreshed from base table for changed LRNOs
SHIP_SEARCH: UPDATE ABSD_SHIP_SEARCH SET BREADTH = C09_MLD where SEQNO='00'
Annotation on C09_EFD group via tblAnnotationLogGeneral

ABSD_HIDE_Update

Table: ABSD_HIDE FOR UPDATE, INSERT Cursor: LRNO+SEQNO

Depth record validation. Cross-validates moulded depth against draught; depth must exceed draught for the same sequence record.

Validation Rules

Cursor iterates over (LRNO, SEQNO) from inserted
C10_MLD (moulded depth) — when > 0, must be > ABSD_HIDR.C07_DL (draught load line) for same LRNO+SEQNO='00'
valStandard4_new applied to C10_EFD
valStandard8 applied to C10_SRCE
valStandard1 applied to C10_VER

DML Actions

J06 audit written to ABSD_OVGE
Staging: ABSD_HIDE_UPDATES refreshed from base table
SHIP_SEARCH: UPDATE ABSD_SHIP_SEARCH SET DEPTH = C10_MLD where SEQNO='00'
Annotation on C10_EFD group

HI* Flag & Registration Triggers

ABSD_HIFL_Update

Table: ABSD_HIFL FOR UPDATE, INSERT Cursor: LRNO+SEQNO

Flag state and port of registry. One of the most interconnected triggers: flag changes cascade EFDs across six OVGE/SUPPLEMENTAL fields, blank registration identifiers when country changes, and sync multiple SHIP_SEARCH fields. EDM migration completed March 2026.

EDM Migration — March 2026 Country code FK validation migrated from ABSD_CBCY to EDM.dbo.T_REF_COUNTRY (COLLATE Latin1_General_CI_AI). Any tooling that queries ABSD_CBCY for flag country validation is now stale.

Validation Rules

valStandard4_new — B04_EFD
valStandard1 — B04_VER
B04_HP_IND — enum: NY
B04_CNTY — FK → EDM.dbo.T_REF_COUNTRY (post Mar 2026)
B04_CNTY + B04_TOWN combined — FK → ABSD_CBTO1 (port of registry)

EFD Cascade — on B04_EFD change

UPDATE ABSD_OVGE SET B01_EFD=@EFD, B02_EFD=@EFD, B10_EFD=@EFD, B11_EFD=@EFD
UPDATE SUPPLEMENTAL_ABSD_OVGE SET MMSI_EFD=@EFD

Country Change Blanking Rule When B04_CNTY changes, the following registration identifiers are nulled/cleared: B11_SATCOM, B01_CALLSIGN, B11_ANSBK_CODE, B02_OFFNO, B10_FISHNO, SUPPLEMENTAL_ABSD_OVGE.FISHNO20, MMSI, MMSI_EFD, SUPPLEMENTAL_ABSD_HIFL.MMSI, SUPPLEMENTAL_ABSD_HIFL.CALLSIGN. This prevents orphaned registration numbers from prior flag states remaining active.

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HIFL_UPDATES refreshed from base table
ABSD_OVNA.FLAG_CDE sync — only for SEQNO='00'
SHIP_SEARCH (SEQNO='00'): SET FLAG=B04_CNTY, PORT=B04_TOWN, PORTNAME=VWPORTDECODE.TOWNNAME, FLAGNAME=VWCOUNTRYDECODE.ALLNAMES
Primary annotation on B04_CNTY group; secondary on B04_TOWN group

ABSD_HIBBC_Update

Table: ABSD_HIBBC FOR UPDATE, INSERT

Bare-boat charter records. Uses the ownership-family date validator and includes a PARALLEL_IND business flag; J06 timestamp records launch time as well as date.

Validation Rules

valStandard9Owner_new — charter EFD dates (ownership-style 8-char format)
PARALLEL_IND — must be Y or N; controls whether this charter runs concurrently with registered ownership

DML Actions

J06: J06_AUTHOR + J06_LNCHDATE + J06_LNCHTIME (time component included — unlike most triggers)
Staging: ABSD_HIBBC_UPDATES refreshed from base table
Annotation on charter EFD group

HI* Classification Triggers

ABSD_HILC_Update

Table: ABSD_HILC FOR UPDATE, INSERT Cursor: LRNO+SEQNO

LR classification status per vessel. Uses ownership-family date format (valStandard9new, not valStandard4_new). LRCL_IND changes trigger mining notation add/delete procedures and update SHIP_SEARCH with full class string.

Validation Rules

valStandard9new — LRCL_EFD (ownership-style date, not valStandard4_new)
LRCL_IND — enum: Y Classed N Not classed W Withdrawn C Cancelled S Suspended T Transferred

Classification Mining Logic (Task 0169/0286)

LRCL_IND changed to W (Withdrawn) → calls spClassNotationMiningDelete
LRCL_IND changed to Y (Classed) → calls spClassNotationMiningAdd using F03_NOT_LINES from ABSD_LRHN

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HILC_UPDATES refreshed from base table
SHIP_SEARCH (SEQNO='00'): SET classsearch = vwClass.classsearch, ClassList = ltrim(dbo.fnclasslist(@LRNO))
Annotation on LRCL_EFD group

ABSD_HIFC1_Update

Table: ABSD_HIFC1 FOR UPDATE, INSERT

Foreign class record 1 (primary entry). J06 and annotations are commented out — delegated to ABSD_FOR_CLASS triggers since September 2006. Only version validation remains active.

FOR_CLASS Delegation (Sep 2006) J06 audit and tblAnnotationLogGeneral writes are commented out in this trigger body. They are handled instead by the ABSD_FOR_CLASS trigger family, which fires on the same table. Both trigger families are registered; ABSD_FOR_CLASS is authoritative for J06/annotations.

Validation Rules

valStandard1 — B09_VER (only active validation)

DML Actions

Staging: refreshes both ABSD_HIFC1_UPDATES and ABSD_HIFC2_UPDATES from their respective base tables for all LRNOs in inserted

ABSD_HIFC2_Update

Table: ABSD_HIFC2 FOR UPDATE, INSERT

Foreign class record 2 (detail entry). Contains NN/UU cross-check between class code and status. SHIP_SEARCH classsearch refreshed from SEQNO='00' only. J06 and annotations remain commented out — FOR_CLASS handles them.

Validation Rules

B09_FOR_CLAS — FK → ABSD_CBUB1 where FLDI='62'
B09_FC_ST — enum: CDNYSU
If B09_FOR_CLAS='NN' then B09_FC_ST must be N
If B09_FOR_CLAS='UU' then B09_FC_ST must be U

DML Actions

Staging: refreshes both ABSD_HIFC1_UPDATES and ABSD_HIFC2_UPDATES
SHIP_SEARCH (SEQNO='00'): SET classsearch = vwClass.classsearch

HI* Propulsion Hierarchy Triggers

ABSD_HIPC_Update

Table: ABSD_HIPC FOR UPDATE, INSERT

Propulsion configuration header. Minimal trigger — J06 audit only. No field validation, no staging, no annotation, no SHIP_SEARCH update.

DML Actions

J06: J06_AUTHOR + J06_LNCHDATE written to ABSD_OVGE
No staging table refresh
No annotation log entries

ABSD_HIPM_Update

Table: ABSD_HIPM FOR UPDATE, INSERT Cursor: LRNO+SEQNO+POSN+TYPE+PM_SEQ

Main propulsion machinery records. Cursor redesigned January 2007 (MRE) to handle composite PK safely. Aggregates KW across all SEQNO='00' engines into SHIP_SEARCH POWER field. J06 conditional — only written if not already set for same author on same date.

Validation Rules

KW — max 5 digits
OE01_RPM — max 4 digits
valStandard4_new — EFD
valStandard8 — SRCE

Conditional J06 Write J06 is only written if no existing ABSD_OVGE record already has the same author on the same date (checked before update). Prevents redundant J06 stamps when a batch updates multiple HIPM rows for one vessel.

DML Actions

Staging: ABSD_HIPM_UPDATES refreshed from base table
SHIP_SEARCH: SET [POWER] = (SELECT SUM(KW) FROM ABSD_HIPM WHERE LRNO=@LRNO AND SEQNO='00')
Annotation on Type group

Commented-Out Logic: HIPU Auto-Creation An earlier version of this trigger attempted to auto-INSERT rows into ABSD_HIPU for SeaWeb integration when new HIPM records were created. This logic is fully commented out in the current trigger body.

ABSD_HIPS_Update

Table: ABSD_HIPS FOR UPDATE, INSERT Cursor: LRNO+SEQNO+POSN+TYPE+PS_SEQ DUAL GUARD

Shaft and propulsion system records. Uses dual-guard pattern — must pass both TRIGGER_DISABLE and TRIGGER_DISABLE_HIPS checks before executing. Cursor redesign January 2007.

Dual Guard Pattern

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE) BEGIN

          IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE_HIPS) BEGIN

            -- trigger body

          END

        END

Both guard tables must be empty for the trigger to fire. TRIGGER_DISABLE_HIPS allows suppressing HIPS specifically without disabling all triggers (e.g. during propulsion data bulk loads).

Validation Rules

KW — max 5 digits
OE01_RPM — max 4 digits
valStandard4_new — EFD
valStandard8 — SRCE

DML Actions

Staging: ABSD_HIPS_UPDATES refreshed from base table
Annotation on Type group

ABSD_HIPU_Update

Table: ABSD_HIPU FOR UPDATE, INSERT Cursor: LRNO+SEQNO+POSN+TYPE+PU_SEQ

Auxiliary propulsion unit records. Most comprehensive numeric validation in the propulsion family — three separate CC/value pairs validated with valStandard3Num. Cursor redesign January 2007.

Validation Rules

POW — max 6 digits
KW — max 5 digits
OE01_RPM — max 4 digits
valStandard4_new — EFD
valStandard8 — SRCE
valStandard3Num — POW_CC/POW pair (CC valid only when value > 0)
valStandard3Num — KW_CCDE/KW pair
valStandard3Num — OE01_RPM_CC/OE01_RPM pair

DML Actions

Staging: ABSD_HIPU_UPDATES refreshed from base table
Annotation on Type group

ABSD_HIPR_Update

Table: ABSD_HIPR FOR UPDATE, INSERT Outer + Inner Cursor

Propeller records. Uses nested cursor pattern — outer cursor over LRNOs, inner cursor calls spValidateD05_PRTYP to validate propeller type against a codebook. Separate FK check on screw/paddle type.

Validation Rules

D05_PRTYP — validated via stored procedure spValidateD05_PRTYP (inner cursor per LRNO)
D05_SCRPA — FK → ABSD_CBUB1 where FLDI='04' (screw/paddle type codebook)
valStandard4_new — D05_EFD
valStandard8 — D05_SRCE

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HIPR_UPDATES refreshed from base table
Annotation on D05_EFD group

HI* Electronic Equipment

ABSD_HISE_Update

Table: ABSD_HISE FOR UPDATE, INSERT

Electronic equipment records. Notable anomaly: the correction-code field D02_CC uses valStandard4_new (date-style validator) instead of the usual valStandard2 used for CC fields across other triggers.

Anomaly: D02_CC uses valStandard4_new Most correction-code fields across the schema use valStandard2. HISE's D02_CC is validated with valStandard4_new (the date validator). This appears intentional — the electronic equipment CC field carries a date-format value rather than a standard correction code.

Validation Rules

valStandard4_new — D02_EFD
valStandard4_new — D02_CC (unusual — see alert)
valStandard1 — D02_VER
valStandard8 — D02_SRCE
D02_TYPE — FK → ABSD_CBUB1 where FLDI='16' (COLLATE SQL_Latin1_General_CP1_CS_AS)

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HISE_UPDATES refreshed from base table
Annotation on D02_EFD group

HI* Tonnage Triggers

ABSD_HITM_Update

Table: ABSD_HITM FOR UPDATE, INSERT

Tonnage measurement records (gross and net tonnage values). Both gross and net have separate CC/value pair validation. Two version fields independently validated.

Validation Rules

B08_GROSS — max 7 digits
B08_NET — max 7 digits
valStandard4_new — B08_EFD, B08_CC
valStandard1 — B08_GR_VER, B08_NET_VER
valStandard8 — B08_SRCE
valStandard3Num — B08_GR_CC/B08_GROSS pair
valStandard3Num — B08_NET_CC/B08_NET pair

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HITM_UPDATES refreshed from base table
Annotation on B08_EFD group

ABSD_HITS_Update

Table: ABSD_HITS FOR UPDATE, INSERT

Tonnage system records — which measurement system (e.g. Suez, Panama) applies. EDM migration April 2026 for tonnage system codebook. Source validation is currently commented out.

EDM Migration — April 2026 (GS) B05_TON_SYS FK validation migrated from ABSD_CBUB3 to EDM.dbo.T_REF_CBUB3 (COLLATE SQL_Latin1_General_CP1_CS_AS) where TABNO='20'.

Validation Rules

valStandard4_new — B05_EFD
valStandard2 — B05_CC
valStandard1 — B05_VER
B05_SRCE validation — COMMENTED OUT in current trigger body
B05_TON_SYS — FK → EDM.dbo.T_REF_CBUB3 TABNO='20' (post Apr 2026)

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HITS_UPDATES refreshed from base table
Annotation on B05_EFD group

ABSD_HITT_Update

Table: ABSD_HITT FOR UPDATE, INSERT

Tonnage type records. Tonnage type codebook also migrated to EDM April 2026. OSD/CSD indicator has a constrained five-value enum.

EDM Migration — April 2026 (GS) B06_TONTYP FK migrated from ABSD_CBUB3 to EDM.dbo.T_REF_CBUB3 where TABNO='21'.

Validation Rules

valStandard4_new — B06_EFD
valStandard8 — B06_SRCE
valStandard2 — B06_CC
valStandard1 — B06_VER
B06_TONTYP — FK → EDM.dbo.T_REF_CBUB3 TABNO='21'
B06_OSD_CSD — enum: O COCNNUU

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HITT_UPDATES refreshed from base table
Annotation on B06_EFD group

HI* Vessel Status — HIST

ABSD_HIST_Update

Table: ABSD_HIST FOR UPDATE, INSERT Cursor: LRNO+SEQNO ~1065 lines — Most Complex HI* Trigger

Vessel status history — the most consequential HI* trigger. A status change cascades into: casualty notations, OWNC/OWST rebuild, SHIP_SEARCH, NB quarter markers, NBEmail flags, thruster/generator/engine status sync, and OVSMC/OVDOC creation on first-'S' entry. Uses valStandard10_new (not valStandard4_new) for EFD. Cursor redesigned December 2006. Updated December 2025 to reference vwABSD_OWXR_EDM for EDM-sourced ownership data.

EDM Live Reference — December 2025 (MW) OWNC/OWST rebuild now uses vwABSD_OWXR_EDM which queries ownership relationship data directly from EDM rather than local ABSD tables. Comment in code: "Updated to use vwABSD_OWXR_EDM which references REL data from EDM directly."

Pre-Cursor Validation (All Rows)

A02_STS — FK → ABSD_CBUB1 where FLDI='19' (checked across all inserted rows before cursor opens)
valStandard10_new — A02_EFD (different from valStandard4_new used by other HI* triggers)
DOB future-date block — if vessel status is NOT in POUFENXZA, DOB may not be in the future
Flag obsolescence check — for in-service statuses POUFESCRLTV, flag country must not be obsolete in ABSD_CBCY

Per-SEQNO Cursor Actions

J06 audit → ABSD_OVGE
Staging: ABSD_HIST_UPDATES refreshed from base table
SHIP_SEARCH (SEQNO='00'): SET STATUSCODE=A02_STS, STATUS=ABSD_CBUB1.TRUD
ABSD_OVNA.SHP_ACT_ST synced to new status

Status-Specific Logic

First entry with A02_STS='S' → auto-create ABSD_OVSMC (safety management certificate) + ABSD_OVDOC (document of compliance) records
Status U → write ABSD_NCON.COMMQTR (commissioning quarter marker)
Status F → write ABSD_NCON.LQTR (laid-up quarter marker)
First status S → write ABSD_NCON.CQTR (completion quarter marker)
Status in POUFE → SUPPLEMENTAL_ABSD_NCON.NBEMAILINDICATOR = '1'
Other statuses → SUPPLEMENTAL_ABSD_NCON.NBEMAILINDICATOR = '0'
Status X (cancelled) → UPDATE SUPPLEMENTAL_ABSD_NCON SET NBEmailIndicator='1'
Transition from P/O/U/E/F → S: write SUPPLEMENTAL_ABSD_NCON.RELEASEDATE
tblFSWNBPage: P→'P', all others→'U'; special case: if transitioning from NB status within 2 months of completion → 'D'

Machinery Status Sync

ABSD_MATH.e95_thst_stat → synced to vessel status (thruster records)
ABSD_HIGE.e45_gs → synced (generator records, SEQNO='00')
ABSD_HIMO.es → synced (engine records, SEQNO='00')

Casualty Auto-Notation When vessel status is set to a loss/disposal code, a casualty notation is automatically created in ABSD_OVCA via spMakeNewCas:

D → notation 'BROKEN UP'
Q → notation 'SCUTTLED'
T → notation 'TO BE BROKEN UP'
W → notation 'TOTAL LOSS'

OWNC / OWST Rebuild on Status Change

Status changes trigger full rebuild of ABSD_OWNC (vessel-company relationships)
ABSD_OWNC populated for RC (registered owner) and MR (manager) relationship types
ABSD_OWST (company fleet counts) recalculated using 5-part IRP/RP/P formula sourced from vwABSD_OWXR_EDM

Annotations

Primary: A02_STS group (status change)
Secondary: A02_EFD group

Cross-Reference History & Tanker Details

ABSD_HRXR_Update

Table: ABSD_HRXR FOR UPDATE, INSERT

History cross-reference records linking vessels to external reference systems. Staging key is a composite of owner/date/cross-reference fields — not LRNO as in all other triggers.

Non-LRNO Staging Key ABSD_HRXR_UPDATES is keyed by OWCODE + OWDATE + XRCODE + XREF_IND rather than LRNO. This reflects the table's role as a cross-reference registry where multiple vessels may share cross-reference codes, and LRNO alone does not uniquely identify records.

DML Actions

J06 audit → ABSD_OVGE (keyed via LRNO from inserted)
Staging: ABSD_HRXR_UPDATES refreshed using composite key
No field validation, no annotations

ABSD_TANKER_Update

Table: ABSD_TANKER FOR UPDATE, INSERT NO TRIGGER_DISABLE GUARD

Tanker-specific hull details (COW, IGS, SBT, coating, hull type). Anomalous: this trigger does NOT check TRIGGER_DISABLE. It always fires regardless of the disable table state. Syncs several FUSF2 (fuel oil system) fields when hull-type and safety system fields change.

No Guard Table Check Unlike every other UPDATE/INSERT trigger in this database, ABSD_TANKER_Update has no IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE) wrapper. It fires unconditionally. Bulk loads or migrations that populate TRIGGER_DISABLE to suppress other triggers will NOT suppress TANKER. Plan accordingly.

FUSF2 Sync Rules

ABSD_FUSF2 fields synced based on TANKER values: hull type, COW indicator, IGS indicator, SBT indicator
J06 written only for non-coating field changes (coating updates are excluded from J06)
No annotation log entries
No staging table

ST* Construction Data Triggers

ABSD_STAL_Update

Table: ABSD_STAL FOR UPDATE, INSERT

Accommodation data. Simple trigger with one EFD validation. A typo exists in the error message — it reads 'A01_EFD' but refers to field D03_EFD.

Validation Rules

valStandard4_new — D03_EFD (error message in trigger incorrectly labels this 'A01_EFD' — typo in original code)

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STAL_UPDATES refreshed from base table
Annotation on D03_EFD group

ABSD_STDE_Update

Table: ABSD_STDE FOR UPDATE, INSERT

Deck data. No field validation. Staging uses inserted directly rather than re-querying the base table — distinct from the pattern used by all other ST* triggers.

Staging from inserted (not base table) INSERT ABSD_STDE_UPDATES SELECT * FROM inserted — Most triggers delete from the updates table then re-select from the base table to get the current committed state. STDE copies directly from the inserted pseudo-table (the triggering rows only), which means STDE_UPDATES reflects only the rows being changed, not the full vessel record set.

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STDE_UPDATES populated from inserted (not base table)
Annotation on C05_EFD group

ABSD_STGE_Update

Table: ABSD_STGE FOR UPDATE, INSERT Cursor: LRNO

General structural geometry. Validates hull height against depth and displacement against physical dimensions (L × B × D × 1.025). Cursor redesign October 2007. SHIP_SEARCH DISPLACEMENT has no SEQNO filter (unlike most other SHIP_SEARCH updates).

Validation Rules

C04_HGHT — when > 0, must be ≥ ABSD_HIDE.C10_MLD (moulded depth) for SEQNO='00'
C08_DSPLCMNT — max 7 digits
C08_DSPLCMNT — must be < LENGTH × BREADTH × DRAFT × 1.025:
Length priority: HILE.C02_LOA → fallback C02_LBP → fallback C02_REG
Breadth priority: HIBR.C09_MLD → fallback C09_EX
Draft: HIDR.C07_DL

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STGE_UPDATES refreshed from base table
SHIP_SEARCH: SET DISPLACEMENT = C08_DSPLCMNT — no SEQNO='00' filter
Three annotation groups: C04_srce, DOB, c08_dsplcmnt

ABSD_STKE_Update

Table: ABSD_STKE FOR UPDATE, INSERT

Keel data. No field validation. Standard pattern: J06, staging from base table, annotation.

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STKE_UPDATES populated from inserted
Annotation on C06_EFD group

ABSD_STSE_Update

Table: ABSD_STSE FOR UPDATE, INSERT Cursor: LRNO+SEQNO DUAL GUARD

Shipbuilder and construction records — the most complex ST* trigger. Dual-guard pattern. Validates builder, standard type, hull material, multiple date formats. When completion date changes on a pre-service vessel, cascades EFDs to 30+ tables across the schema.

Dual Guard Pattern Requires both TRIGGER_DISABLE and TRIGGER_DISABLE_STSE to be empty. TRIGGER_DISABLE_STSE allows suppressing only the completion-date cascade during targeted construction data migrations without disabling all triggers.

Validation Rules

C01_BLD_COMP — mandatory; FK → ABSD_CBSB composite key (KEYC+KEYG+KEYN+KEYX); also FK → absd_cbcoaddr
C01_STD_TYP_CODE — FK → ABSD_OVGE (i.e. must be an LRNO of an existing vessel — standard type is defined by reference to another vessel)
C01_SUFFX — must not be set unless C01_YRDNO also has a value
C01_MLD_CC — enum: MHUNY; values M/H/Y require hull material in {RP, FC, WP, SC, YY}
valStandard8 — C01_SRCE
valStandard1 — C01_YRD_VER
valStandard9new — C01_LNCH_DT, C01_DTH_DT, C01_COMP_DT
valStandard4_new — C01_COMM_DT, C01_EFD

Hull Material Propagation

When C01_HUL_MAT changes to RP or AL → UPDATE ABSD_FUCO2 SET D09_MAT='NN'
All other hull materials → UPDATE ABSD_FUCO2 SET D09_MAT='ST'

Completion Date Cascade (C01_COMP_DT change on SEQNO='01', pre-service vessel) When the completion date changes for a pre-service vessel (first shipbuilder record), the new date propagates as the EFD to every major data table. Tables updated (SEQNO='00' unless noted):

ABSD_FTME.j01_date
ABSD_FTNT.j05_efd
ABSD_FTQY.j02_date
ABSD_FUCA1.d08_efd
ABSD_FUCO1.d09_efd
ABSD_FUDI1.d22_efd + d21_efd
ABSD_FUDO1.d17_efd
ABSD_FUGE.d06+d10+d18+d25_efd
ABSD_FUHA1.d16_efd
ABSD_FULC.d14_efd
ABSD_FULI1.d19_efd
ABSD_FURO1.d13_efd + d23_efd
ABSD_FUSF.d07_efd
ABSD_FUST1.d12_efd
ABSD_FUTO.d20_efd
ABSD_FUUN1.d11_efd
ABSD_HIBR.c09_efd (SEQNO='00')
ABSD_HIDE.c10_efd (SEQNO='00')
ABSD_HIDR.c07_efd (SEQNO='00')
ABSD_HIFL.b04_efd (SEQNO='00')
ABSD_HIGE.E45_efd+dm (SEQNO='00')
ABSD_HILE.c02_efd (SEQNO='00')
ABSD_HIMO.efd+dm (SEQNO='00')
ABSD_HIMT (first SEQNO='00')
ABSD_HIOW.h01_efd (8-char, SEQNO='00')
ABSD_HIOP.operator_efd (8-char, SEQNO='00')
ABSD_SPTM.efd (8-char)
ABSD_HIMA.h02_efd (8-char, SEQNO='00')
ABSD_HISM.efd (8-char, SEQNO='00')
ABSD_HIPP_dated.h03_efd (using HIOW EFD, SEQNO='00')

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STSE_UPDATES refreshed from base table
Primary annotation on C01_BLD_COMP group

ABSD_STST_Update

Table: ABSD_STST FOR UPDATE, INSERT

Superstructure data. Most validation-heavy ST* trigger. Fourteen OD_TYP-specific cross-checks, eight CC fields with zero-value rules, and multiple mutual exclusion pairs between deck structure fields.

Standard Field Validation

valStandard4_new — C03_EFD
valStandard8 — C03_SRCE
valStandard1 — C03_VER
C03_OD_TYP — must be alphanumeric only (no special characters)

Deck Structure Fields — CC Rules

Each deck structure field (POOP, BRIDGE, FOCSLE, BR_AFT, BR_FO, BR_PO, RQDK, RADK, RFDK, RSDK, TRUNK, TR_AFT, TR_FO, TR_WEL) has an associated CC field validated with valStandard2. When C03_OD_TYP=0, all CC fields must be set to 'N'.

When C03_OD_TYP = 0: all deck structure CC fields must be N
C03_OD_LTH — must be > 0 unless C03_OD_LTH_CC is set; C03_OD_TYP must have a value when C03_OD_LTH is set
C03_BR_STM — must be > 0 unless C03_BR_STM_CC = 'Y'

Mutual Exclusion Pairs

POOP ↔ BRIDGE (cannot both be present)
BRIDGE ↔ BR_FO
BRIDGE ↔ BR_PO
POOP ↔ BR_PO
FOCSLE ↔ BR_FO
BR_AFT ↔ BR_PO
TRUNK ↔ TR_AFT
TRUNK ↔ TR_FO
TRUNK ↔ TR_WEL

OD_TYP Cross-Checks (14 rules)

A → BRIDGE required
B or C → RQDK required
D → POOP and FOCSLE both excluded
E → POOP and RQDK both excluded
F → BR_FO excluded
I → FOCSLE or BR_FO required (at least one)
L → BR_FO required
P → BRIDGE and RQDK both excluded
Q → FOCSLE and RQDK both excluded
R → POOP required
W → POOP required
2 → BRIDGE required
8 → at least one of BR_AFT, BRIDGE, POOP required

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_STST_UPDATES populated from inserted
Annotation on C03_EFD group

LR* Classification & Survey Triggers

LREQ1_Insert

Table: ABSD_LREQ1 INSTEAD OF INSERT, UPDATE

LR equipment requirements record 1. Uses INSTEAD OF pattern — the trigger intercepts the INSERT/UPDATE, deletes the existing record, then inserts the new values. This is the full-replace pattern used by LREQ1, LRHI, and LRHN.

INSTEAD OF Pattern (Full Replace) DELETE FROM ABSD_LREQ1 WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_LREQ1 SELECT * FROM inserted
The trigger replaces rather than updates — no partial-field update semantics. All fields must be supplied on every write.

Validation Rules

valStandard4_new — F08_EFD
valStandard8 — F08_SRCE

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LREQ1_UPDATES refreshed from base table after replace

ABSD_LREQ2_Update

Table: ABSD_LREQ2 FOR UPDATE, INSERT

LR equipment requirements record 2 — cable/graduation data. FK validation on cable grade codebook.

Validation Rules

F08_CABGRAD — FK → ABSD_CBUB1 where FLDI='48' (cable grade)

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LREQ2_UPDATES refreshed from base table
Annotation on F08_CAB_GRAD group

ABSD_LREQ3_Update

Table: ABSD_LREQ3 FOR UPDATE, INSERT

LR equipment requirements record 3 — anchor weight data. Cross-check prevents dual unit entry.

Validation Rules

F08_ANC_WGT_MET and F08_ANC_WGT_IMP cannot both be > 0 — metric and imperial anchor weights are mutually exclusive

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LREQ3_UPDATES refreshed from base table
Annotation on F08_ANC_NO group

ABSD_LREQ4_Update

Table: ABSD_LREQ4 FOR UPDATE, INSERT

LR equipment requirements record 4 — fee and equipment symbols. Two symbol fields with constrained three-value enums.

Validation Rules

F08_FEE_SYMB — must be +, *, or blank
F08_EQIP_SYM — must be +, *, or blank

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LREQ4_UPDATES refreshed from base table
Annotation on F08_EQIP_LET group

ABSD_LRGE_Update

Table: ABSD_LRGE FOR UPDATE, INSERT

LR general data — the most comprehensive LR* trigger. Covers multiple data groups (F02 through F31) each with independent EFD/SRCE/CLS validation. Three inter-table cross-checks enforce classification consistency. Seven annotation groups.

Field Group Validation

F02 (Service Type)

valStandard4_new — F02_EFD
valStandard8 — F02_SRCE
F02_STYP1 — enum: CSSSDRD4HTLKTDT4RD
F02_STYP2 — enum: SSTD
F02_MOD — valStandardYN

F04 (Machinery)

valStandard4_new — F04_EFD
valStandard8 — F04_SRCE
F04_MBT — enum: *+REBNA
F04_RULES — enum: LBNOM
F04_STAT — enum: CS

F05 (Hull Classification)

valStandard4_new — F05_EFD
valStandard8 — F05_SRCE
F05_CLS — valStandardYN
F05_STAT — S or blank

F07 / F28 (Additional Class)

F07_CLS, F28_CLS — valStandardYN
F07_STAT, F28_STAT — S or blank

F06 (Class Society)

valStandard4_new — F06_EFD
valStandard8 — F06_SRCE
F06_CLS — enum: L+LG+GR+MNNUU
F06_STAT — S or blank

F31 (DPS)

valStandard4_new — F31_EFD
valStandard8 — F31_SRCE
F31_DPS — enum: AAAMNNCM

DOB / F29

DOB — valStandard4_new
F29_EFD, F29_SRCE

Inter-Table Cross-Checks

F02_EFD year must not precede the year of ABSD_STSE.C01_EFD — the general LR record cannot pre-date the shipbuilder record
F05_CLS='Y' requires F04_MBT to be set, unless ABSD_LRHN.F03_S_STAT='C' (cancelled notation exempts from machinery requirement)
F06_CLS ≠ 'NN' when ABSD_OVTY.D01_INT_BASIC LIKE '_3___' OR '__3__' (vessel type requires LNG classification) then ABSD_FUGE.D10_LQG must be > 0

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LRGE_UPDATES populated from inserted
Seven annotation groups: DOB, F02_EFD, F04_EFD, F05_EFD, F06_EFD, F07_EFD, F28_EFD, F29_EFD, F31_EFD

LRHI_Insert

Table: ABSD_LRHI INSTEAD OF INSERT, UPDATE

LR historical data. INSTEAD OF pattern (full replace). Contains a suspected copy-paste artefact in the pre-delete guard condition.

Possible Copy-Paste Artefact The trigger checks IF EXISTS (SELECT * FROM ABSD_FTNT WHERE LRNO IN (SELECT LRNO FROM inserted)) before deleting from ABSD_LRHI. This condition references ABSD_FTNT (a fuel type table) and appears to be a copy-paste error — it likely should reference ABSD_LRHI itself. The effect is that the delete may be skipped if no FTNT record exists for the vessel.

Validation Rules

valStandard4_new — F17_EFD
valStandard8 — F17_SRCE
valStandard2 — F17_SPIND

DML Actions (INSTEAD OF)

Delete existing ABSD_LRHI records for LRNO (subject to FTNT guard — see alert)
Insert from inserted pseudo-table
J06 audit → ABSD_OVGE
Staging: ABSD_LRHI_UPDATES refreshed

LRHN_Insert

Table: ABSD_LRHN INSTEAD OF INSERT, UPDATE

LR hull notation. INSTEAD OF pattern. F03_NOT_LINES changes trigger class notation mining procedures. Multiple ice class fields with a shared enum. Freeboard indicator uses valStandardYN.

Validation Rules

valStandard4_new — F03_EFD
valStandard8 — F03_SRCE
F03_ICE_CLS1, F03_ICE_CLS2, F03_MI_CLASS — enum: 1*123NI1S1A1B1C1DCEABCDSNSFNN
F03_IC1_STAT, F03_IC2_STAT, F03_MI_STAT — must be S or blank
F03_FRBRD — valStandardYN

Classification Mining Logic (Task 0169/0286)

When F03_NOT_LINES changes AND ABSD_HILC.LRCL_IND='Y' for this LRNO:
→ Calls spClassNotationMiningDelete then spClassNotationMiningAdd
This keeps the class notation mining tables in sync with hull notation changes

DML Actions (INSTEAD OF)

Delete existing ABSD_LRHN records for LRNO
Insert from inserted pseudo-table
J06 audit → ABSD_OVGE
Staging: ABSD_LRHN_UPDATES refreshed

ABSD_LRSC_Update

Table: ABSD_LRSC FOR UPDATE, INSERT

LR survey codes. Nullable FK on survey code codebook. Direction indicator (D/E) has a cross-check: E-type records must not carry a date.

Validation Rules

F11_CODE — FK → ABSD_CBUB1 where FLDI='57' (nullable — only checked when NOT NULL)
F11_DIND — enum: D (due) E (exemption)
If F11_DIND='E', F11_DATE must not be set — exemptions have no due date
F11_DATE format validation — commented out in current trigger body

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LRSC_UPDATES refreshed from base table
Annotation on F11_CODE group

ABSD_LRSU_Update

Table: ABSD_LRSU FOR UPDATE, INSERT

LR survey records. Uses valStandardYMD for EFD — a distinct date format not used by any other trigger in this series. Source validation is commented out.

valStandardYMD — Distinct Date Format F11_EFD is validated with valStandardYMD (year-month-day format). All other triggers in this database use valStandard4_new, valStandard9new, or valStandard10_new for date fields. LRSU is the only trigger using valStandardYMD.

Validation Rules

valStandardYMD — F11_EFD
F11_SRCE validation — commented out

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LRSU_UPDATES refreshed from base table
Annotation on F11_EFD group

ABSD_LRWD_Update

Table: ABSD_LRWD FOR UPDATE, INSERT

LR working draft data. Uses valStandard9new (ownership-style 8-char date) for EFD, but only when the value is not '00000000' — the zero date is a valid placeholder that bypasses format validation.

Validation Rules

valStandard9new — F09_EFD (only validated when F09_EFD ≠ '00000000')
valStandard8 — F09_SRCE

DML Actions

J06 audit → ABSD_OVGE
Staging: ABSD_LRWD_UPDATES refreshed from base table
Annotation on F09_EFD group

Trigger Cross-Reference

Trigger	Guard	Pattern	Date Validator	Staging Table	SHIP_SEARCH Field(s)	Notable Rules
`HIBR_Update`	Standard	Cursor LRNO+SEQNO	valStandard4_new	HIBR_UPDATES	BREADTH	Cross-check vs HIDE depth
`HIDE_Update`	Standard	Cursor LRNO+SEQNO	valStandard4_new	HIDE_UPDATES	DEPTH	Cross-check vs HIDR draft
`HIFL_Update`	Standard	Cursor LRNO+SEQNO	valStandard4_new	HIFL_UPDATES	FLAG, PORT, PORTNAME, FLAGNAME	EDM Mar 2026; EFD cascade; country-change blanking
`HIBBC_Update`	Standard	Set-based	valStandard9Owner_new	HIBBC_UPDATES	—	LNCHTIME in J06; PARALLEL_IND enum
`HILC_Update`	Standard	Cursor LRNO+SEQNO	valStandard9new	HILC_UPDATES	classsearch, ClassList	Mining add/delete on LRCL_IND change
`HIFC1_Update`	Standard	Set-based	—	HIFC1+HIFC2_UPDATES	—	J06/annotations handled by FOR_CLASS
`HIFC2_Update`	Standard	Set-based	—	HIFC1+HIFC2_UPDATES	classsearch	NN/UU → FC_ST cross-check; FOR_CLASS delegation
`HIPC_Update`	Standard	Set-based	—	—	—	J06 only; minimal trigger
`HIPM_Update`	Standard	Cursor 5-key	valStandard4_new	HIPM_UPDATES	POWER (SUM KW)	Conditional J06; KW max 5 digits
`HIPS_Update`	DUAL (TRIGGER_DISABLE_HIPS)	Cursor 5-key	valStandard4_new	HIPS_UPDATES	—	Dual guard pattern
`HIPU_Update`	Standard	Cursor 5-key	valStandard4_new	HIPU_UPDATES	—	3x valStandard3Num pairs; POW max 6 digits
`HIPR_Update`	Standard	Outer+Inner cursor	valStandard4_new	HIPR_UPDATES	—	spValidateD05_PRTYP; D05_SCRPA FLDI='04'
`HISE_Update`	Standard	Set-based	valStandard4_new	HISE_UPDATES	—	D02_CC uses valStandard4_new (anomaly)
`HITM_Update`	Standard	Set-based	valStandard4_new	HITM_UPDATES	—	2x valStandard3Num pairs; GROSS/NET max 7 digits
`HITS_Update`	Standard	Set-based	valStandard4_new	HITS_UPDATES	—	EDM Apr 2026; B05_SRCE validation commented out
`HITT_Update`	Standard	Set-based	valStandard4_new	HITT_UPDATES	—	EDM Apr 2026; OSD_CSD 5-value enum
`HIST_Update`	Standard	Cursor LRNO+SEQNO	valStandard10_new	HIST_UPDATES	STATUSCODE, STATUS	Most complex: OWNC/OWST rebuild, casualty auto-notation, quarter markers, NB email
`HRXR_Update`	Standard	Set-based	—	HRXR_UPDATES (composite key)	—	Non-LRNO staging key
`TANKER_Update`	NONE	Set-based	—	—	—	Always fires; FUSF2 sync; J06 on non-coating only
`STAL_Update`	Standard	Set-based	valStandard4_new	STAL_UPDATES	—	Error message labels D03_EFD as 'A01_EFD' (typo)
`STDE_Update`	Standard	Set-based	—	STDE_UPDATES (from inserted)	—	Stages from inserted, not base table
`STGE_Update`	Standard	Cursor LRNO	—	STGE_UPDATES	DISPLACEMENT (no SEQNO filter)	L×B×D×1.025 displacement cross-check
`STKE_Update`	Standard	Set-based	—	STKE_UPDATES	—	No validation
`STSE_Update`	DUAL (TRIGGER_DISABLE_STSE)	Cursor LRNO+SEQNO	valStandard9new + valStandard4_new	STSE_UPDATES	—	Completion date cascade to 30+ tables; hull material propagation
`STST_Update`	Standard	Set-based	valStandard4_new	STST_UPDATES	—	14+ OD_TYP rules; 9 mutual exclusion pairs
`LREQ1_Insert`	Standard	INSTEAD OF	valStandard4_new	LREQ1_UPDATES	—	Full delete+insert replace
`LREQ2_Update`	Standard	Set-based	—	LREQ2_UPDATES	—	CABGRAD FK FLDI='48'
`LREQ3_Update`	Standard	Set-based	—	LREQ3_UPDATES	—	Metric/imperial anchor weight mutual exclusion
`LREQ4_Update`	Standard	Set-based	—	LREQ4_UPDATES	—	FEE_SYMB and EQIP_SYM: +/* or blank only
`LRGE_Update`	Standard	Set-based	valStandard4_new	LRGE_UPDATES	—	7+ annotation groups; 3 cross-checks; F02–F31 groups
`LRHI_Insert`	Standard	INSTEAD OF	valStandard4_new	LRHI_UPDATES	—	FTNT guard artefact; full replace
`LRHN_Insert`	Standard	INSTEAD OF	valStandard4_new	LRHN_UPDATES	—	spClassNotationMining on NOT_LINES change; ice class enum
`LRSC_Update`	Standard	Set-based	—	LRSC_UPDATES	—	DIND=E excludes F11_DATE; F11_CODE nullable FK
`LRSU_Update`	Standard	Set-based	valStandardYMD	LRSU_UPDATES	—	Only trigger using valStandardYMD
`LRWD_Update`	Standard	Set-based	valStandard9new	LRWD_UPDATES	—	EFD validated only when ≠ '00000000'