10f – Cargo & Capacity Triggers

Purpose

ABSD_FUCA1 holds the single cargo capacity summary record per vessel. The trigger validates the effective date and source code, writes the J06 audit stamp, refreshes the staging shadow table, and logs a secondary annotation.

Business Rules & Validations

• D08_EFD (effective date) — validated via valStandard4_new; must be a valid YYMMDD date.
• D08_SRCE (source code) — validated via valStandard8; must match an approved source lookup value.

Downstream Actions

• J06 audit — updates ABSD_OVGE.J06_AUTHOR + J06_LNCHDATE for all inserted LRNOs.
• Staging — refreshes ABSD_FUCA1_UPDATES from inserted (not the base table).
• Secondary annotation — fires unconditionally; group lookup: Tablename=ABSD_FUCA1, Fieldname=D08_EFD.

Purpose

ABSD_FUCA2 holds per-cargo-type detail rows for a vessel (multiple rows per LRNO). Each row records a stowage mode and commodity combination. The trigger enforces referential integrity against two CBUB1 codebook tables.

Business Rules & Validations

If either FK check fails, the entire transaction is rolled back with an error message.

Downstream Actions

• J06 audit — writes to ABSD_OVGE for affected LRNOs.
• Staging — refreshes ABSD_FUCA2_UPDATES from the base table (SELECT * FROM ABSD_FUCA2 WHERE LRNO IN (SELECT LRNO FROM inserted)), not from inserted — captures the full post-update row set.
• Secondary annotation — fires unconditionally; group lookup: Tablename=ABSD_FUCA2, Fieldname=D08_TOWAGE.
• ShipWatch — commented-out ABSD_AWSH_UPDATES integration exists but is disabled.

DELETE Trigger

ABSD_FUCA2_DELETE fires unconditionally (no TRIGGER_DISABLE guard) and only writes the J06 audit entry from the deleted pseudo-table.

Purpose

ABSD_FUCO1 holds the single container data overview record per vessel (overall containerisation capability). ABSD_FUCO2 holds the detail rows per container size/type.

Business Rules & Validations

Downstream Actions

• J06 audit — writes to ABSD_OVGE.
• Staging — refreshes ABSD_FUCO1_UPDATES from inserted.
• Secondary annotation — unconditional; group: Tablename=ABSD_FUCO1, Fieldname=D09_EFD.

FUCO2

The detail trigger follows the same delete-only-J06 pattern for FUCO2_DELETE. The update trigger adds validation similar to FUCA2 but for container-type-specific fields.

Purpose

Records vessel dimensions relevant to manoeuvrability: bow thruster diameter, manoeuvring bow length. The trigger was redesigned in October 2007 to use a cursor loop over inserted to support bulk updates (multiple vessels in one statement).

Pre-Cursor Validation (fires once for the whole batch)

Per-Cursor Validations (per LRNO)

Downstream Actions (per cursor iteration)

• J06 audit — updates ABSD_OVGE for the current INDEXLRNO.
• Staging — refreshes ABSD_FUDI1_UPDATES from inserted WHERE LRNO = @INDEXLRNO.
• Two annotation groups:
  • Fires when D21_efd or D21_srce changes → group: ABSD_FUDI1/D21_efd
  • Fires when D22_srce, D22_dis_diam, D22_efd, D22_man_typ, or D22_manbow_len changes → group: ABSD_FUDI1/D22_srce

FUDI2, FUDI3, FUDI4 Detail Tables

Each has a DELETE trigger (unconditional J06 only) and an UPDATE trigger following the same validation-staging-annotation pattern for their respective dimension fields.

Purpose

Records vessel draft, freeboard, and loadline assignment zone data. FUDO1 holds the summary; FUDO2 holds per-loadline zone detail rows.

Pattern

Follows the standard FU* pattern: EFD via valStandard4_new, SRCE via valStandard8, VER via valStandard1, CC fields via valStandard2, CC/value pairs via valStandard3Num. J06 audit, staging refresh from inserted, secondary annotation on the EFD group.

ABSD_FUDO2_DELETE is unconditional J06 only.

Purpose

The most complex cargo trigger. ABSD_FUGE is the central general-data record per vessel, carrying passenger berthing capacities, crew numbers, hold cargo volumes (grain, bale, liquid, gas, oil), number of winches, nuclear power indicator, and auxiliary propulsion indicator. Changes to capacity and passenger fields flow directly into ABSD_SHIP_SEARCH.

Pre-Cursor Digit-Length Validations (batch, fire once)

Per-Cursor Validations (per LRNO)

Cross-Check: Winch Count Requires Electric Lifting Gear

IF @PASSEDNUM > 0 AND @PASSEDNUMTWO > 0 -- FULI2 rows with non-electric type
RAISERROR('D18_WNCHS_NO cannot be > 0 unless D19_LG_TYP is DE or AF', 16, 1)

SHIP_SEARCH Update

UPDATE ABSD_SHIP_SEARCH SET
  GASCAP      = D10_LQG,
  INSULATEDCAP= D10_INULTD,
  LIQUID      = D10_LQD,
  GRAIN       = D10_GRAIN,
  BALE        = D10_BALE,
  PASSENGERS  = (D06_PASS_DK + D06_PASS_BR + D06_PASS_UN),  -- sum of all three berth types
  OILCAP      = D10_OIL
FROM ABSD_SHIP_SEARCH INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO
WHERE ABSD_SHIP_SEARCH.LRNO = @INDEXLRNO

Secondary Annotation Groups (6 groups)

Purpose

Records hatch data summary for a vessel (hatch count, dimensions, cover type). ABSD_FUHA2 holds per-hatch detail rows.

Business Rules & Validations

Downstream Actions

• J06 audit, staging (ABSD_FUHA1_UPDATES from inserted), secondary annotation on D16_EFD group.

FUHA2

ABSD_FUHA2_DELETE is unconditional J06 only. The update trigger validates per-hatch fields and refreshes ABSD_FUHA2_UPDATES.

Purpose

Records liquid cargo capacity in detail (e.g. total liquid cargo capacity with units). Single row per vessel.

Business Rules & Validations

Downstream Actions

J06 audit, staging (ABSD_FULC_UPDATES from inserted), secondary annotation on D14_EFD group.

Purpose

ABSD_FULI1 holds the lifting gear overview record per vessel (total number of cranes, derricks, SWL totals). ABSD_FULI2 and ABSD_FULI3 hold per-piece lifting gear detail. Note: FUGE.D18_WNCHS_NO (winch count) cross-checks FULI2 D19_LG_TYP — see FUGE documentation above.

Business Rules & Validations

Downstream Actions

J06 audit, staging (ABSD_FULI1_UPDATES from inserted), secondary annotation on D19_EFD group.

FULI2 / FULI3 Detail Tables

Both have unconditional DELETE triggers (J06 only) and UPDATE triggers validating per-crane/derrick fields. D19_LG_TYP in FULI2 is constrained to codes in a lifting gear type codebook and is referenced by the FUGE winch count cross-check.

Purpose

Records offshore vessel-specific features (e.g. dynamic positioning, deck strength, moonpool). Unusually, all three DML events (UPDATE, INSERT, DELETE) are combined in a single trigger named ABSD_FUOF_All.

Logic

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE)
BEGIN
    UPDATE dbo.ABSD_OVGE
    SET J06_AUTHOR = UPPER(LEFT(RIGHT(SYSTEM_USER, ...), 3)),
        J06_LNCHDATE = ...
    WHERE ABSD_OVGE.LRNO IN (
        SELECT LRNO COLLATE Latin1_General_CI_AS FROM inserted
    );
END

Note the use of COLLATE Latin1_General_CI_AS (case-insensitive) on the LRNO join — unlike most FU* triggers which use no explicit collation on the LRNO join.

Purpose

Records Ro-Ro vessel lane configuration: number of lanes, lane length/width/height, vehicle and lorry capacities. Used for ferry and car carrier data quality.

Digit-Length Validations

Format Validations

Cross-Check: Lane Length vs. LOA

SELECT @PASSEDNUM = D13_LANE_LEN / D13_LANES_NO FROM inserted
SELECT @PASSEDNUMTWO = C02_LOA FROM ABSD_HILE WHERE LRNO IN (SELECT LRNO FROM inserted) AND SEQNO='00'
IF @PASSEDNUM > @PASSEDNUMTWO
    RAISERROR('D13_LANE_LEN / D13_LANES_NO cannot exceed C02_LOA, in this case ' + CAST(@PASSEDNUMTWO AS VARCHAR(8)), 16, 1)

Downstream Actions

• J06 audit, staging (ABSD_FURO1_UPDATES from inserted).
• 3 annotation groups: D13_Lane_LEN (on lane length change); D13_Lanes_No (on count/EFD/dimensions changes); D23_EFD (on D23 section changes).

Purpose

ABSD_FUSF holds up to 15 ship feature/equipment codes per vessel (e.g. crane type, deck equipment, special facilities). Each code slot is independently validated against the ship features codebook.

Business Rules & Validations

IF UPDATE(D07_CODE_N) AND (SELECT COUNT(*) FROM inserted WHERE D07_CODE_N IS NOT NULL) > 0
BEGIN
    IF NOT (SELECT COUNT(*) FROM ABSD_FUSF, ABSD_CBUB1
            WHERE ABSD_FUSF.D07_CODE_N COLLATE SQL_Latin1_General_CP1_CS_AS = ABSD_CBUB1.CODE
              AND ABSD_CBUB1.FLDI = '12'
              AND LRNO IN (SELECT LRNO FROM INSERTED)) > 0
    RAISERROR('The code in D07_CODE_N does not match a record in table 12 of CBUB1', 16, 1)
END

Downstream Actions

J06 audit, staging (ABSD_FUSF_UPDATES from inserted), secondary annotation on D07_EFD group.

ABSD_FUSF2_DELETE is unconditional J06 only.

Purpose

Records general ship particulars (cumulative capacity, special designations). The trigger is minimal — it contains no field-level validation.

Downstream Actions

• J06 audit, no staging table refresh.
• Secondary annotation: group lookup Tablename=ABSD_FUSP, Fieldname=CAP_CUM.

FUSP DELETE Trigger

Unconditional J06 only (no TRIGGER_DISABLE guard).

Purpose

Records cargo segregation data: how many segregated cargo spaces a vessel has. D12_CARGO_SEG (number of segregated segments) must pass a complex validation routine delegated to a stored procedure.

Business Rules & Validations

Downstream Actions

• J06 audit per cursor iteration.
• Staging: ABSD_FUST1_UPDATES from inserted WHERE LRNO = @INDEXLRNO.
• Secondary annotation: group D12_EFD, fires unconditionally per cursor iteration.

Purpose

Records anchor chain/rope, towing gear, and bollard pull data for vessels — particularly relevant for tugs and offshore support vessels. Has a second guard table TRIGGER_DISABLE_FUTO for bulk anchor data operations.

Guard Structure

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE)   -- outer: global enable
BEGIN
    IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE_FUTO)  -- inner: per-operation enable
    BEGIN
        -- all validation and cascade logic
    END
END

Digit-Length Validations

Format Validations

Cross-Check: Anchor Type Requires Correct Vessel Type

D20_AT_TYP1 IN ('A','T','C','Y')
  AND D01_INT_BASIC NOT LIKE '__4__'        -- not xx4xx (offshore/tug)
  AND D01_TYP_A/B/C/D/E NOT IN ('224')     -- not offshore code 224
→ RAISERROR('D20_AT_TYP1 cannot be A T C or Y unless D01_INT_BASIC is xx4xx or D01_TYP=224')

Cross-Check: Rope Length vs. HIBR Extreme Breadth

SELECT @PASSEDNUMTWO = C09_EX FROM ABSD_HIBR WHERE LRNO IN (SELECT LRNO FROM INSERTED) AND SEQNO='00'
IF @PASSEDNUM > @PASSEDNUMTWO
    RAISERROR('D20_AT_SRLEN must be less than C09_EX, in this case ' + CAST(@PASSEDNUMTWO AS VARCHAR(20)), 16, 1)

Removed Rule (Jul 2009)

A previously active check that required D20_BLLRD_PL > 0 only for vessels with D01_INT_BASIC LIKE '__4__' or TYP codes 224, 065, 066, 067, 261, 264, 265, 262 was removed at the request of Michael Ayers on 23/07/2009. The commented-out code remains as documentation.

FK Validation

Downstream Actions

J06 audit, staging (ABSD_FUTO_UPDATES from inserted), secondary annotation on D20_EFD group.

Purpose

Records container/TEU capacity per vessel: total TEU capacity, reefer plug count, and up to three location codes (H=Hold, R=Ramp, D=Deck) indicating where containers can be stowed. The location codes have strict sequential and ordering constraints.

Digit-Length Validations

Format Validations

Location Code FK Validation (EDM)

Location Sequence Constraint

IF D11_LOC_3 IS NOT NULL AND (D11_LOC_2 IS NULL OR D11_LOC_2 IN ('U','Y','N'))
    → ERROR: 'The codes in D11_LOC must be filled in in sequence'
IF D11_LOC_2 IS NOT NULL AND (D11_LOC_1 IS NULL OR D11_LOC_1 IN ('U','N','Y'))
    → ERROR: same

Location Ordering Constraint (H → R → D)

Permitted single values:   H, R, D
Permitted two-value combos: HH, HR, HD, RR, RD, DD
Permitted three-value combos: HHH, HHR, HRR, HRD, HHD, HDD, RRR, RRD, RDD, DDD

Downstream Actions

• J06 audit, staging (ABSD_FUUN1_UPDATES from inserted).
• Conditional annotation group selection (unique pattern among FU* triggers):
  • If D11_REF_CP is being updated → group: ABSD_FUUN1/D11_REF_CP
  • Else if D11_TEU_CAP is being updated → group: ABSD_FUUN1/D11_TEU_CAP
  • Else → group: ABSD_FUUN1/D11_EFD
  This ensures the annotation is logged under the most relevant grouping category for the type of change.

FUUN2

ABSD_FUUN2_DELETE is unconditional J06 only. The ABSD_FUUN2_Update trigger validates per-container-type unit load fields.