Trigger Business Rules — LR Maritime Database

Universal: J06 Last-Modified Stamp

Every INSERT/UPDATE/DELETE trigger on every ABSD_ table updates the parent vessel's last-modified fields. This is the primary audit timestamp mechanism.

UPDATE ABSD_OVGE SET
  J06_AUTHOR   = UPPER(LEFT(RIGHT(system_user, LEN(system_user)-CHARINDEX('\\',system_user)), 3)),
  J06_LNCHDATE = RIGHT(YEAR(GETDATE()),2)
               + REPLICATE('0',2-LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS VARCHAR(2))
               + REPLICATE('0',2-LEN(DAY(GETDATE()))) + CAST(DAY(GETDATE()) AS VARCHAR(2))
WHERE LRNO IN (SELECT LRNO FROM inserted)

J06_AUTHOR extracts the last 3 characters after the backslash from the Windows login (e.g., DOMAIN\usr → USR). J06_LNCHDATE stores YYMMDD format.

Universal: *_UPDATES Staging Tables

Every trigger also inserts the changed record into a parallel ABSD_*_UPDATES table which acts as a change queue for downstream systems (APS, Seaweb, reporting pipelines). Every modification to any ABSD_ table creates a staging record.

Universal: Annotation / Audit Logging

Triggers call spAnnotateChange to log field-level changes to tblChanges. The English-readable field name is retrieved via spGetEnglishFieldName. Secondary groupings are logged to tblAnnotationLogGeneral.

Mechanism	Table	Content
Primary field log	`tblChanges`	Table name, English field name, old value, new value, user initials, source reference
Secondary groupings	`tblAnnotationLogGeneral`	Groups of related field changes for presenting annotation summaries

Universal: Cursor Pattern for Batch Operations

Many triggers use a cursor to handle batch insert/update operations correctly (since inserted pseudo-table may contain multiple rows):

DECLARE cursor_name INSENSITIVE CURSOR FOR
  SELECT LRNO FROM inserted
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @CURR_RECORD
WHILE @@FETCH_STATUS = 0
BEGIN
  -- per-record validation and cascade logic
  FETCH NEXT FROM cursor_name INTO @CURR_RECORD
END
CLOSE cursor_name
DEALLOCATE cursor_name

ABSD_OVGE_Update — Vessel General Data (UPDATE, INSERT)

Validation Rules

Date of Build (DOB)

Future DOB not allowed if vessel status (A02_STS from ABSD_HIST) is NOT in ('P','O','U','F','E','N','X','Z','A')
Pre-delivery and pre-order status codes are exempt from this check

Call Sign (B01_CALLSIGN)

Validated against flag country call sign ranges in EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES
For flags SZI, FIJ, EGY, SUD: first 3 characters of callsign are checked
For all other flags: first 2 characters are checked
Calls fnCallsignCheck(@Callsign, @Flag) — returns 1=valid, 0=invalid

Official Number (B02_OFFNO)

If vessel is flagged Panama: official number cannot contain a hyphen
Validated for length and format per flag-specific rules

Publication Status (A03_PUBST)

Only values D, U, Y, S, X, Z are permitted
Any other value raises an error and rolls back

Equipment Check (F01_EQPT_CHK)

Must be 1, E, N, -, or blank

Built-to-Mark (B01_BLT_TO_MK)

Must be +, *, R, or blank

Cross-Table Updates

ABSD_SHIP_SEARCH: B01_CALLSIGN → CALLSIGN, B02_OFFNO → OFFICIALNO, B10_FISHNO → FISHINGNO
ABSD_SUPPLEMENTAL_HIFL: Callsign change triggers insert to supplemental flag history
ABSD_OVGE_UPDATES: Staging table populated on every change

Audit Logging

Logs changes to tblChanges for: B01_CALLSIGN, B02_OFFNO, B10_FISHNO, DOB. Secondary groupings to tblAnnotationLogGeneral.

ShipDel trigger (DELETE on ABSD_OVGE): Currently disabled/no-op (logic commented out). When enabled, would cascade delete to all related vessel tables.

ABSD_OVNA_Update — Vessel Name History (UPDATE, INSERT)

Name Validation Rules

G01_NAME for SEQ '00' MUST be UPPERCASE
No trailing spaces allowed in G01_NAME
Name cannot match any existing historical name for the same vessel
Cannot have consecutive identical names (checks sequences N-1, N, N+1 for duplicates)
Only ONE record may have G01_LNCHD_AS='Y' (launched as) per vessel
Only ONE record may have G01_COMP_AS='Y' (completed as) per vessel
At least ONE record must have G01_ORIG_NAME='Y' or 'U'
G01_HULL_SEC must match a valid code in ABSD_CBUB1 where FLDI='42'
G01_EFD cannot be prior to C01_EFD in ABSD_STSE (construction date) — unless vessel has multiple hull sections (rebuilt)

Cross-Table Updates

ABSD_SHIP_SEARCH: VESSELNAME updated from G01_NAME (SEQ '00' only)
ABSD_AWSH_UPDATES: ShipWatch staging updated when G01_ORIG_NAME='Y'
ABSD_OVNA_UPDATES: Staging populated on every change
ABSD_OVGE: J06 fields updated

ABSD_HIOW_Update — Registered Owner History (∼1,000 lines — MOST COMPLEX)

⚠

This is the largest and most complex trigger in the database at approximately 1,000 lines of T-SQL. It orchestrates ownership changes across 8+ tables simultaneously.

1. Owner-Manager Synchronization

When new H01_OWNER_CODE equals current manager (ABSD_HIMA.H02_MANAGER):

If new owner is NOT '9991001': system prevents the same entity being both owner and manager
Archives the current HIMA record: calls SPMAKEHIMAHISTORICAL(@LRNO)
Inserts @@spid into trigger_disable_hima to prevent recursive trigger firing
Updates ABSD_HIMA to set H02_MANAGER = '9991001' (placeholder "no manager" code)
Deletes own spid from trigger_disable_hima

When old owner equals current ship manager (ABSD_HISM): archives old HIMA and updates HIMA manager to the ship manager (HISM) value.

2. Dual Certification Paths (HIPP + HITP)

Company Type	Condition	HIPP Gets	HITP Gets
GROUP_OWGE='A'	CONF='N' or NULL	Owner code	'9991001' (placeholder)
GROUP_OWGE='A'	CONF is set	'9991001' (placeholder)	Owner code
Non-Group A	Any	Traverses vwABSD_OWXR_EDM up to 2 levels for BO (Beneficial Owner) with PUBLIND_1 in ('P','R','I','Z'). Falls back to '9991001' if no publishable path found.

3. ABSD_OWST Aggregation (full recalculation)

Deletes and fully recalculates fleet count statistics for the affected owner:

IRP_OWNED: ships where rel1_1='RC' (registered company)
IRP_MANAGED: ships where any of rel1_1/2/3='MR' (manager)
IRP_AGENTFOR / RP_AGENTFOR / P_AGENTFOR: complex relationship + PUBLIND combinations

4. ABSD_OWSH / ABSD_OWNC Maintenance

Maintains Registered Company (RC) and Manager (MR) relationships
Can hold up to 3 relationships per owner-ship link (REL1_1, REL1_2, REL1_3)
When updated, old relationship is demoted to position 2 or 3
If no relationships remain after update, owner record is deleted

5. Search Index Update

Updates ABSD_SHIP_SEARCH: OWNERCODE, OWNER (short name from OWGE), OWNERCOD (nationality NATY1)

6. Audit

Logs old/new owner codes to tblChanges. Marks as "Made historical" (new insert) or "Historical amendment" (update to non-00 seq).

ABSD_HIOW_DELETE: Prevents delete that would create duplicate consecutive owner entries. Raises error if adjacent records have same owner code. Updates J06 fields.

ABSD_HIMA_Update — Manager History (UPDATE, INSERT)

H02_MANAGER cannot equal current registered owner (ABSD_HIOW.H01_OWNER_CODE at SEQNO='00') unless both are '9991001'
When manager changes, cascades updates to ABSD_OWSH (relationship records) and ABSD_OWST (statistics)
Updates SUPPLEMENTAL_ABSD_HIMA with changes from external feed sources
Uses trigger_disable_hima to prevent recursive firing when called from HIOW trigger

ABSD_CAGE1_Update — Casualty/Incident (UPDATE, INSERT)

Format Validation

INNO (incident number): must be null or exactly 5 zero-padded characters
KNO (key number): same rule as INNO
STD/ENDD (dates): validated via valStandard9Non99
STDS (start date source): validated against ABSD_CBUB1 table 20
STCS (circumstance code): validated against ABSD_CBUB1 table 22

Demolition/Disposal Logic

DISP Value	Action
`'D'` or `'X'` (demolition)	Verifies or creates ABSD_CADI disposal record. Updates SUPPLEMENTAL_ABSD_CADI. Error if multiple demolition records for same vessel.
`'C'` (casualty)	If CADI disposal record exists for this incident, it is deleted. Cannot coexist with demolition.
Set DISP='C'	BLOCKED if a CADI disposal record already exists for this vessel

Automated Cross-Vessel Incident Creation

When OLR_1, OLR_2, or OLR_3 (other vessel LR numbers) are populated: automatically creates matching CAGE1 incident records for each referenced vessel, ensuring multi-vessel incidents are recorded symmetrically.

Cascade Updates

ABSD_CALA: When STD or LRNO changes → updates EDITOR (user initials) and EDIT_DATE (YYYYMMDD)
ABSD_OWCA: Deletes and recreates owner-casualty associations using vwcreateabsd_owca when STD or LRNO changes
ABSD_CAGE1_UPDATES: Staging table updated on every modification

CasDel trigger (DELETE): Inserts deleted records into ABSD_CASDEL_UPDATES with user initials and deletion timestamp.

ABSD_CBCOROOT_Update — Company/Builder Root (UPDATE, INSERT)

Complex multi-type processing based on OUT_TYPE value:

OUT_TYPE	Processing
`'F'` — Flag code	Deletes/re-inserts ABSD_CBEB records. Applies name spacing rules based on OUT_STATUS (T=Trading, N=Non-trading, W=Withdrawn).
`'T'` — Type table	Deletes/re-inserts ABSD_CBSB (shipbuilder codebook): KEYC, KEYG, KEYN, KEYX, STYLE, DECODEA, DECODEB. Inserts BLDR_TEXT Type 'A' (address) from up to 6 CBCOADDR records. Inserts BLDR_TEXT Type 'P' (phone/contact) from CBCOCOMM.
`'S'` — Shipbuilder	Processes CBCONOTE lines (seq 10–19=location, 20–49=history, 01=notes). Inserts BLDR_NAME with CNTRY, CDE1/2, CD, NAME_ST, PORT, BLD_NAME, BLD_LOC. Inserts BLDR_TEXT H (history) and N (notes). Parses 70-char BLDR_XREF field → 10×6-char builder codes → inserts BLDR_XREF records.
`'A'` — Address	Updates address line records

Short Name Proper-Case: Updates SUPPLEMENTAL_ABSD_CBCOROOT.SHORT_NAME_PCASE with dbo.proper(OUT_SHORT_NAME) for all OUT_TYPE values.

ABSD_CBPPROOT_Update — Port/Place Root (UPDATE, INSERT)

Format Validation

KEY must be exactly 7 characters
Country + Port Sequence must be unique
Grid reference (OUT_LLP_PORT_CODE) must be unique if populated

Automatic Table Synchronization

Target Table	Updated Fields	Logic
`ABSD_CBTO1`	CNTRY, TOWN, TCNT (LR Office), TNS1, TNS2	Town name split at last space within 19 chars: TNS1 (≤19 chars), TNS2 (remainder)
`ABSD_CBPD`	CNTRY, TOWN, VER, PORTLOC, MARSDEN, SISZONE, lat/long components	MARSDEN computed via `fnMarsdenGrid`; SISZONE via `fnSISZone`; VER='C' if lat degree valid (0–360)

ABSD_FUGE_Update — Vessel Capacity (UPDATE, INSERT)

Field Length Validations (max digits enforced)

Field	Max Digits
`D18_WNCHS_NO`	2
`D06_PASS_UN, D06_PASS_DK, D06_PASS_BR, D06_CREW_NO, D06_CBNS, D06_DVBR`	4 each
`D10_GRAIN, D10_BALE, D10_INULTD, D10_LQD, D10_LQG, D10_OIL, D10_SEG_BLLST, D10_CLN_BLLST, D10_VRT_BALE, D10_OTHER_CAP`	8 each

Enumeration Validations

D10_HTNG_CLS: must be C, P, N, U, or Y (null allowed)
D25_AUX_PROP, D25_NUCLR_PWR: must be N, U, or Y (null allowed)

Cross-Table Constraint

!

D18_WNCHS_NO > 0 is only valid if ABSD_FULI2 (seq 01) has D19_LG_TYP = 'DE' or 'AF' (dedicated winch vessel lifting gear types).

Search Index Update

Updates ABSD_SHIP_SEARCH with: GASCAP, INSULATEDCAP, LIQUID, GRAIN, BALE, PASSENGERS (=D06_PASS_DK+D06_PASS_BR+D06_PASS_UN), OILCAP

ABSD_HIGE_Update — Generator/Electrical Equipment (UPDATE, INSERT)

Field Validations

E45_NO: max 2 digits; E45_KW, E45_VOLT: max 5 digits each; E45_FREQ: max 2 digits
E45_ACDC must be A, D, N, U, or Y (if not null)
E45_ACDC='A' (AC) requires E45_FREQ IN ('50','60','0')
E45_DM (manufacturer date) must be ≤ E45_EFD — UNLESS E45_DM='198099' (special null sentinel)
E45_ST > E45_EFD only allowed if E45_GS='8' (specific generator supertype)
E45_PM_POS_1 through E45_PM_POS_9: each must match ABSD_CBUB1 table 03 codes (if populated)
E45_GM (generator manufacturer): must match valid ABSD_CBEB.MM codes
E45_GS (generator supertype): must match ABSD_CBUB1 table 19 codes, or be '8'

TANKER_UPDATE — Tanker Features (INSERT, UPDATE)

1. Tank Coating Validation (Pre-loop fast rejection)

⚠

Parent-required rule: CARGO_TANK_COATING MUST be 'Y' if any of: COAT_EPOXY, COAT_EPOXY_PHEN, COAT_ZS, COAT_POLY, COAT_SS, or COAT_RUBBER is 'Y'. Same rule applies to SLOP_TANK_COATING and its sub-coating flags. Error: "Cargo tank coating may not be null if one or more cargo tank coats is specified"

2. Hull Type → FUSF2 Special Feature Code Management

HULL_TYPE	Code 3010 (double bottom)	Code 3020 (double side)
`NULL` or `'SH'` (single hull)	Removed	Removed
`'DBP'` (double bottom + port)	Removed	Kept
`'DSP'` (double side + port)	Kept	Removed
`'DH'` (double hull)	Kept	Kept

Uses cursor; calls spDELETEFUSF2(@LRNO, @SEQ) to physically remove FUSF2 records.

3. Loading System Cross-Checks

STERN_LOADING='Y' requires STERN_DISCHARGE='Y' (symmetry rule)
BOW_LOADING='Y' requires BOW_DISCHARGE='Y' (symmetry rule)

4. Other Validations

COW/IGS/SBT/VRS: values validated against permitted code lists
IMO_CHEM_CLASS_I/II/III: validated against ABSD_CBIC codebook values

SUPPLEMENTAL Table Triggers

All SUPPLEMENTAL_ triggers follow the same merge pattern: validate data quality from external feed, then merge back to main ABSD_ table where quality is sufficient:

Trigger	Action
`SuppOVGEUpdate`	Validates and merges changes from SUPPLEMENTAL_ABSD_OVGE back to ABSD_OVGE
`SUPP_FUGE_UPDATE`	Merges supplemental capacity data to ABSD_FUGE
`SUPP_ABSD_CBCY_UPD/DEL`	Maintains supplemental country attributes
`SuppNCONUPdate`	Merges newbuilding data from external feeds
`SuppSTSEUpdate`	Merges supplemental survey data to ABSD_STSE
`SUPP_STDE_UPDATE`	Merges supplemental deadweight data
`SUPPLEMENTAL_ABSD_HIFC1_All`	Manages supplemental classification history
`SUPPLEMENTAL_ABSD_HIFL_All`	Manages supplemental flag history from AIS
`UpdateShortName`	On SUPPLEMENTAL_ABSD_CBCOROOT: applies proper-case to company short names

OFAC Sanctions Triggers

OFAC_INSERT / OFAC_DELETE on ofac_sdn2 (Office of Foreign Assets Control SDN list):

On INSERT of new SDN list entry: marks affected vessels and companies in ABSD_SHIP_SEARCH compliance fields. Cross-references by vessel name, IMO number, and company name.
On DELETE: removes sanction flags from ABSD_SHIP_SEARCH

Standard FU* / HI* Trigger Pattern

Most FU* and HI* table triggers follow an 8-step standard template:

Check TRIGGER_DISABLE — exit immediately if any row present
Validate field lengths and enumeration values
Run cursor loop for batch operations
Validate cross-table dependencies (e.g., EFD sequence order)
Update ABSD_OVGE J06 fields (J06_AUTHOR, J06_LNCHDATE)
Populate *_UPDATES staging table
Log to tblAnnotationLogGeneral (secondary groupings)
Call spAnnotateChange for primary field audit trail

Tables following this pattern: ABSD_FUCA1/2, ABSD_FUCO1/2, ABSD_FUHA1/2, ABSD_FULI1/2/3, ABSD_FURO1/2, ABSD_FUSF/FUSF2, ABSD_FUSP, ABSD_FUST1/2, ABSD_FUTO, ABSD_FUUN1/2, ABSD_HIDR, ABSD_HIDE, ABSD_HILE, ABSD_HILC, ABSD_HIMO, ABSD_HIMT, ABSD_HIPC, ABSD_HIPM, ABSD_HIPP, ABSD_HIPS, ABSD_HIPU, ABSD_HISE, ABSD_HISM, ABSD_HIBS, ABSD_HITM, ABSD_HITP, ABSD_MAAU, ABSD_MABO, ABSD_MABU, ABSD_MAEM1/2/3, ABSD_MAGR, ABSD_MAPR, ABSD_MASP, ABSD_MAST, ABSD_MATH, ABSD_STAL, ABSD_STDE, ABSD_STGE, ABSD_STSE, ABSD_LREQ1–4, ABSD_LRSC, ABSD_LRSU, ABSD_LRWD.

Trigger-Based Business Rules

Validation Rules

Date of Build (DOB)

Call Sign (B01_CALLSIGN)

Official Number (B02_OFFNO)

Publication Status (A03_PUBST)

Equipment Check (F01_EQPT_CHK)

Built-to-Mark (B01_BLT_TO_MK)

Cross-Table Updates

Audit Logging

Name Validation Rules

Cross-Table Updates

1. Owner-Manager Synchronization

2. Dual Certification Paths (HIPP + HITP)

3. ABSD_OWST Aggregation (full recalculation)

4. ABSD_OWSH / ABSD_OWNC Maintenance

5. Search Index Update

6. Audit

Format Validation

Demolition/Disposal Logic

Automated Cross-Vessel Incident Creation

Cascade Updates

Format Validation

Automatic Table Synchronization

Field Length Validations (max digits enforced)

Enumeration Validations

Cross-Table Constraint

Search Index Update

Field Validations

1. Tank Coating Validation (Pre-loop fast rejection)

2. Hull Type → FUSF2 Special Feature Code Management

3. Loading System Cross-Checks

4. Other Validations