10h — Casualty Triggers

CA* · HA* — Incident records, disposal records, contacts, location, adverse history
Casualty Schema Architecture All CA* tables share a composite key of LRNO + INNO (incident number). ABSD_CAGE1 is the master incident record; all other CA* tables are child records. Every CA* and HA* trigger writes ABSD_CALA.EDITOR + ABSD_CALA.EDIT_DATE as a cross-table audit timestamp — this is the "last touched" stamp used by the APS system (Production_Casualties table). It is separate from the J06 audit on ABSD_OVGE.

Core Casualty Triggers

ABSD_CALA_Update

Table: ABSD_CALA FOR UPDATE, INSERT No guard — active body is self-update only

Casualty location/attributes — the anchor record for each incident. The trigger body was almost entirely disabled when Events 3.0 went live in July 2016. The only active code is a self-referencing EDITOR/EDIT_DATE stamp. All validation, geo-conversion, and staging logic is commented out.

Events 3.0 Go-Live — July 2016 The original CALA trigger contained: coordinate completeness validation (start/end/disposal lat/lon pair integrity), decimal position conversion via fnConvertDegreesMinsToDecimal, Marsden Grid derivation via fnMarsdenGrid, SIS Zone via fnSISZone, cascaded updates to ABSD_CAGE1.MGS/MGE/ZNES/ZNEE and ABSD_CADI.MG/ZONE, ABSD_CALA_UPDATES staging, and annotation logging. All of this was commented out at Events 3.0 cutover. The old code also had a dual guard (TRIGGER_DISABLE + TRIGGER_DISABLE_CALA).
Active Code — APS Fix (MW, 01/02/2017) The only active line: UPDATE ABSD_CALA SET EDITOR=..., EDIT_DATE=... on the rows being inserted/updated. This self-update ensures APS can detect the change via EDIT_DATE when querying Production_Casualties. There is no TRIGGER_DISABLE guard on this active code — it always fires.

ABSD_CAGE1_Update

Table: ABSD_CAGE1 FOR UPDATE, INSERT Most complex casualty trigger

Master incident record. Controls incident type (casualty vs. disposal), date validation, other-vessel linking, auto-creation of disposal records, OLR cascade to other vessels, and OWCA rebuild. Old set-based version replaced with scalar-variable version for performance July 2010 (MRE). Note: EDIT_DATE on ABSD_CALA is the timestamp used by APS — updated on every CAGE1 write (MW comment 19/12/2017).

Pre-Execution Guard
  • Standard TRIGGER_DISABLE guard
  • Additional check: IF (SELECT INNO FROM inserted) IS NOT NULL — trigger body only executes when an incident number is present
Validation Rules
  • DISP IN ('C','X') and REM IS NULL → RAISERROR: "The Removal From Scene must be filled for a casualty"
  • STD (start date, nullable) — valStandard9Non99 (differs from valStandard9new — rejects '99' dates)
  • ENDD (end date, nullable) — valStandard9Non99
  • OLR_1, OLR_2, OLR_3 (other vessels involved, nullable) — each must exist in ABSD_OVGE
  • STDS (start situation, nullable) — FK → ABSD_CBUB1 FLDI='20' (COLLATE SQL_Latin1_General_CP1_CS_AS)
  • STCS (start cause/circumstances, nullable) — FK → ABSD_CBUB1 FLDI='22' (COLLATE SQL_Latin1_General_CP1_CS_AS)
  • DISP IN ('D','X') — only one demolition/disposal record per vessel allowed; RAISERROR if a second is attempted
  • KNO (killed number, nullable) — must be NULL or exactly 5 characters (zero-padded, e.g. 00018)
  • INO (missing number, nullable) — must be NULL or exactly 5 characters
DISP Type Logic
  • C (Casualty): if a ABSD_CADI disposal record exists for this LRNO+INNO, delete it and save INNO to TEMPCADI — a casualty cannot have a disposal record
  • D or X (Demolition/Disposal): if no ABSD_CADI record exists, auto-create one: INSERT INTO ABSD_CADI (LRNO, INNO) + matching SUPPLEMENTAL_ABSD_CADI row
  • Cannot set DISP='C' if a ABSD_CADI record already exists (checked after all other logic)
OLR Cascade (Other Vessel Linking)
  • For each of OLR_1, OLR_2, OLR_3 that has a value:
  • If no ABSD_CAGE1 record exists for the OLR vessel+INNO combination:
  • → spDeleteCasualty(@OLRLRNO, @OLRINNO) — clears any remnant records
  • → spCreateNewCasualtyExisitingINNO(@OLRLRNO, @OLRINNO, @LRNO, OLR_position) — creates a mirror incident for the other vessel using the same INNO
  • This ensures multi-vessel collisions are recorded on both vessels
DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated (APS audit trail)
  • On LRNO or STD change: rebuild ABSD_OWCA via vwcreateabsd_owca for this LRNO+INNO
  • Staging: ABSD_CAGE1_UPDATES from inserted
  • Annotation on INNO group

CasDel

Table: ABSD_CAGE1 FOR DELETE No TRIGGER_DISABLE guard

Delete trigger on CAGE1. Captures deleted incident records into a deletion staging table for downstream processing. Always fires regardless of TRIGGER_DISABLE state.

DML Actions
  • No TRIGGER_DISABLE check — fires unconditionally on every DELETE
  • Staging: ABSD_CASDEL_UPDATES populated with LRNO, INNO, and editor initials from deleted pseudo-table
  • No J06 update, no annotations

ABSD_CAGE2_Update

Table: ABSD_CAGE2 FOR UPDATE, INSERT

Incident supplemental data (second general record). One date validation field. Staging from base table (not inserted — consistent with most non-casualty triggers).

Validation Rules
  • EDTE (event date, nullable) — valStandard9new
DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CAGE2_UPDATES from base table (SELECT FROM ABSD_CAGE2, not from inserted)
  • Annotation on INNO group

Casualty Detail Triggers

ABSD_CADI_Update

Table: ABSD_CADI FOR UPDATE, INSERT Cursor: LRNO+INNO

Disposal/demolition detail record. Calculates total scrap price from light displacement tonnage and AMNT when LDT data is available. Three date fields validated. Active ShipWatch integration (B05 column).

Validation Rules
  • DATE_1, DATE_2, DATE_3 (all nullable) — each validated with valStandard9new when NOT NULL
Scrap Price Calculation (Cursor per LRNO+INNO)
  • On AMNT change: if SUPPLEMENTAL_ABSD_HITL.LDT is set for this vessel (SEQNO='00')
  • → UPDATE SUPPLEMENTAL_ABSD_CADI SET TOTAL_SCRAP_PRICE = LDT × (AMNT / 100)
  • AMNT is stored as a scaled integer; the right-9-chars cast extracts the numeric value: CAST(RIGHT(AMNT,9) AS INT)
DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CADI_UPDATES from inserted (not base table)
  • ShipWatch: ABSD_AWSH_UPDATES.B05 = 'Y' — INSERT if no existing row, UPDATE if row exists
  • Annotation on INNO group
Commented-Out ShipWatch Column An earlier version also set ABSD_AWSH_UPDATES.C01='Y'. This is commented out in the current trigger — only B05 is active.

ABSD_CACO_Update

Table: ABSD_CACO FOR UPDATE, INSERT Cursor: LRNO+INNO

Casualty contact/source records. On TEXT_1 update for SNO='01', auto-creates an ABSD_CAPR (precis) record if one does not already exist and the incident is not a disposal (DISP≠'D').

Auto-Creation Logic (Cursor per LRNO+INNO)
  • On TEXT_1 update, when SNO='01':
  • If no existing ABSD_CAPR record for this LRNO+INNO, AND ABSD_CAGE1.DISP ≠ 'D':
  • → INSERT INTO ABSD_CAPR (LRNO, INNO, LNS1, LNS2, LNS3, LNS4) SELECT... from inserted
DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CACO_UPDATES keyed by LRNO+INNO+SNO
  • Annotation on SCE_DATE group

ABSD_CACT_Update

Table: ABSD_CACT FOR UPDATE, INSERT

Casualty action text lines. No field validation. Standard CALA audit, staging, and annotation pattern.

DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CACT_UPDATES keyed by LRNO+INNO
  • Annotation on LNS group

ABSD_CAGC_Update

Table: ABSD_CAGC FOR UPDATE, INSERT

Casualty general contact data. No field validation. Standard CALA audit, staging, and annotation pattern.

DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CAGC_UPDATES keyed by LRNO+INNO
  • Annotation on INNO group

ABSD_CAPO_Update

Table: ABSD_CAPO FOR UPDATE, INSERT

Casualty port data. No validation. Staging uses straight DELETE+INSERT without EXISTS check (unlike most triggers that check first).

DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CAPO_UPDATES — unconditional DELETE then INSERT from inserted
  • Annotation on INNO group

ABSD_CAPR_Update

Table: ABSD_CAPR FOR UPDATE, INSERT

Casualty precis/narrative lines. No validation. Note: CAPR records can also be auto-created by the CACO trigger when source text is entered for SNO='01'.

DML Actions
  • ABSD_CALA EDITOR + EDIT_DATE updated
  • Staging: ABSD_CAPR_UPDATES from inserted
  • Annotation on INNO group

Historic Adverse History Triggers (HA*)

HAAD / HACO Key Structure Unlike CA* tables, HAAD and HACO are keyed by OWCODE (company code), not LRNO. They record adverse history at the company level. J06 audit is written to ABSD_OVGE using a LRNO derived from inserted/deleted, but the staging table key is the company-based composite. Both tables have DELETE triggers that write J06 without checking TRIGGER_DISABLE.

ABSD_HAAD_Update + ABSD_HAAD_DELETE

Table: ABSD_HAAD FOR UPDATE, INSERT / FOR DELETE

Historic adverse address records. Company-level adverse address history. The DELETE trigger writes J06 without a TRIGGER_DISABLE guard.

UPDATE/INSERT Trigger
  • Standard TRIGGER_DISABLE guard
  • No field validation
  • J06 audit → ABSD_OVGE keyed by LRNO from inserted
  • Staging: ABSD_HAAD_UPDATES keyed by OWCODE + OWDATE + SEQNOA; but staging SELECT is FROM ABSD_HAAD WHERE OWCODE IN (...) — fetches all rows for the company, not just changed rows
  • No annotations
DELETE Trigger
  • No TRIGGER_DISABLE guard — fires unconditionally on every DELETE
  • J06 audit → ABSD_OVGE keyed by LRNO from deleted
  • No staging update on delete

ABSD_HACO_Update + ABSD_HACO_DELETE

Table: ABSD_HACO FOR UPDATE, INSERT / FOR DELETE

Historic adverse contact records. Five-part composite staging key. Same pattern as HAAD for delete trigger.

UPDATE/INSERT Trigger
  • Standard TRIGGER_DISABLE guard
  • No field validation
  • J06 audit → ABSD_OVGE
  • Staging: ABSD_HACO_UPDATES keyed by OWCODE + OWDATE + OWSEQN + TYPE + SEQN (5-part); SELECT from ABSD_HACO WHERE OWCODE IN (...) — all rows for company refreshed
  • No annotations
DELETE Trigger
  • No TRIGGER_DISABLE guard — fires unconditionally
  • J06 audit → ABSD_OVGE keyed by LRNO from deleted

Trigger Cross-Reference

Trigger Guard Key Validation Staging Table Notable Rules
CALA_UpdateNONE (active code)LRNO+INNO—— (disabled Jul 2016)Self-update only; full body commented out at Events 3.0
CAGE1_UpdateStandard + INNO IS NOT NULLLRNO+INNOvalStandard9Non99, OLR FKs, STDS/STCS codebook FKs, KNO/INO lengthCAGE1_UPDATESDISP type logic; OLR cascade; OWCA rebuild; CADI auto-create/delete
CasDelNONELRNO+INNO—CASDEL_UPDATESFOR DELETE; always fires; captures editor initials
CAGE2_UpdateStandardLRNO+INNOEDTE valStandard9newCAGE2_UPDATES (from base table)—
CADI_UpdateStandardLRNO+INNODATE_1/2/3 valStandard9newCADI_UPDATES (from inserted)Scrap price calc: LDT × AMNT/100; ShipWatch B05
CACO_UpdateStandardLRNO+INNO+SNO—CACO_UPDATESAuto-creates CAPR on TEXT_1 when SNO='01' and DISP≠'D'
CACT_UpdateStandardLRNO+INNO—CACT_UPDATES—
CAGC_UpdateStandardLRNO+INNO—CAGC_UPDATES—
CAPO_UpdateStandardLRNO+INNO—CAPO_UPDATESUnconditional DELETE+INSERT (no EXISTS check)
CAPR_UpdateStandardLRNO+INNO—CAPR_UPDATESCan be auto-populated by CACO trigger
HAAD_UpdateStandardOWCODE+OWDATE+SEQNOA—HAAD_UPDATESCompany-keyed; staging fetches all rows for OWCODE
HAAD_DELETENONEOWCODE——FOR DELETE; always fires; J06 only
HACO_UpdateStandardOWCODE+OWDATE+OWSEQN+TYPE+SEQN—HACO_UPDATESCompany-keyed; 5-part staging key
HACO_DELETENONEOWCODE——FOR DELETE; always fires; J06 only