10e – Machinery Triggers

Main propulsion engine trigger. Uses a 4-column composite key (LRNO, SEQNO, POS_CODE, TYPE). In April 2026, two codebook lookups were migrated from ABSD_CBUB3 and ABSD_CBUB1 to EDM.dbo.T_REF_CBUB3 and EDM.dbo.T_REF_CBUB1 respectively. Includes automatic creation of sister records in ABSD_HIPU, ABSD_HIPS, and ABSD_HIPM.

1. Pre-Cursor (Batch) Validations

Mandatory Fields

Field Length Constraints

Enumerated Fields

Cross-Field Ordering (Stroke Hierarchy)

• OE01_ST2 > 0 requires OE01_ST1 > 0. Error: "OE01_ST2 cannot contain a value if OE01_ST1 is 0"
• OE01_ST_EX > 0 requires OE01_ST2 > 0. Error: "OE01_ST_EX cannot contain a value if OE01_ST2 is 0"

Codebook FK Lookups (EDM tables — Updated Apr 2026)

2. Per-Cursor (Per-Row) Validations

3. Cascade: HIPU / HIPS / HIPM Auto-Creation

-- For each (LRNO, SEQNO, POS_CODE, TYPE) in inserted:
IF NOT EXISTS (SELECT * FROM ABSD_HIPU WHERE LRNO=@L AND POSN=@P AND SEQNO=@S AND TYPE=@T)
  INSERT INTO ABSD_HIPU (LRNO, POSN, SEQNO, TYPE, PU_SEQ) VALUES (..., '00')

IF NOT EXISTS (SELECT * FROM ABSD_HIPS WHERE LRNO=@L AND POSN=@P AND SEQNO=@S AND TYPE=@T)
  INSERT INTO ABSD_HIPS (LRNO, POSN, SEQNO, TYPE, PS_SEQ) VALUES (..., '00')

IF NOT EXISTS (SELECT * FROM ABSD_HIPM WHERE LRNO=@L AND POSN=@P AND SEQNO=@S AND TYPE=@T)
  INSERT INTO ABSD_HIPM (LRNO, POSN, SEQNO, TYPE, PM_SEQ) VALUES (..., '00')

Business Rule: Every main engine record must have corresponding HIPU (propulsion unit), HIPS (propulsion system), and HIPM (propulsion machinery) records. These are automatically created as shell records when a HIMO record is first inserted.

4. J06 Audit Stamp

Conditional update — only writes J06 if the combination of J06_AUTHOR + J06_LNCHDATE does not already match the current user and today's date for this LRNO. Updates J06_AUTHOR and J06_LNCHDATE only (no J06_LNCHTIME). Uses SYSTEM_USER directly.

5. Staging Table

ABSD_HIMO_UPDATES — refreshed with the full set of current HIMO rows for the LRNO (delete+insert from base table).

6. Annotations

Generator set trigger. Cursor key: (LRNO, SEQNO, POS_CODE). Records electrical generation data per engine position. The HIST trigger sets E45_GS (generator status) when vessel status changes.

1. Pre-Cursor Validations

Field Length Constraints

AC/DC and Frequency Cross-Checks

• E45_ACDC: IN('A','D','N','U','Y') when not null
• When E45_ACDC='A' (Alternating Current): E45_FREQ must be IN('50','60','0'). Error: "E45_FREQ must be 0,50 or 60 when E45_ACDC is A"

Date Cross-Check

• E45_DM (manufactured date) must NOT be > E45_EFD (effective date). Exception: E45_DM='198099' is a sentinel value meaning "pre-1980" and is allowed to exceed EFD. Error: "E45_DM may not be greater than E45_EFD"
• When E45_GS='8' (retired/scrapped): E45_EFD must NOT be > E45_ST (stop date). Error message reuses the "E45_DM" phrasing (a copy-paste bug in the original code).

Codebook FK Lookups

2. Per-Cursor Validations

3. J06 / Staging / Annotations

• J06: conditional update, J06_AUTHOR + J06_LNCHDATE only
• Staging: ABSD_HIGE_UPDATES — refreshed per LRNO
• Secondary annotation: Tablename='ABSD_HIGE', Fieldname='E45_EFD' — always fires per cursor row
• No SHIP_SEARCH update

Main engine motor / turbine details trigger. Unlike most machinery triggers, HIMT was not rewritten as a cursor trigger in 2007. It processes the full inserted set as a batch. The commented-out ABSD_AWSH_UPDATES block (ShipWatch integration) was deactivated.

1. Field Validations

Length Constraints

Enumerated Fields

Validation SPs

Codebook Lookups

2. J06 / Staging / Annotations

• J06: J06_AUTHOR + J06_LNCHDATE only (non-conditional — always updates); uses SYSTEM_USER directly
• Staging: ABSD_HIMT_UPDATES
• Secondary annotation: Tablename='ABSD_HIMT', Fieldname='E54_EFD'
• No SHIP_SEARCH update

Thruster data trigger. Cursor key: (LRNO, SEQ, TYPE). Contains a special business rule that syncs the thruster status field with the vessel's current live status when a new thruster is added.

1. Pre-Cursor Validations

Length Constraints

Thruster Type and Number Rules

2. Per-Cursor Validations

Multi-Zero Constraint

• If E95_THST_NO=0 for the current row AND another row already exists for the same LRNO with a different SEQ and E95_THST_NO=0: RAISERROR "Number of Thrusters (E95_THST_NO) must not be 0 if another 0 record exists"

3. Thruster Status Auto-Sync

4. J06 / Staging / Annotations

• J06: conditional, J06_AUTHOR + J06_LNCHDATE only
• Staging: ABSD_MATH_UPDATES
• Secondary annotation: Tablename='ABSD_MATH', Fieldname='E95_POSSHIP_1'
• No SHIP_SEARCH update

Auxiliary generator trigger. Cursor key: (LRNO, SEQ, TYPE). Contains AC/DC and frequency cross-validation rules that are more complex than HIGE's equivalent.

1. Pre-Cursor Validations

Length Constraints

AC/DC and Frequency Cross-Checks

Codebook

• E89_GEN_MAKE: must be in ABSD_CBEB.MM (generator manufacturer)

2. Per-Cursor Validations

3. J06 / Staging / Annotations

• J06: conditional, J06_AUTHOR + J06_LNCHDATE only
• Staging: ABSD_MAAU_UPDATES
• Secondary annotation: Tablename='ABSD_MAAU', Fieldname='E89_EFD'
• No SHIP_SEARCH update

Emergency engine #1 trigger. Cursor key: (LRNO, SEQ, TYPE). The only machinery trigger with a dual guard. Contains a cross-table check against ABSD_OVTY for the landing fan indicator.

1. Guard Architecture

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE)
BEGIN
  IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE_MAEM1)
  BEGIN
    -- all logic
  END
END

The inner TRIGGER_DISABLE_MAEM1 guard is used to suppress the MAEM1 trigger during bulk operations that update multiple emergency engine records programmatically.

2. Pre-Cursor Validations

Length Constraints

Propulsion Secondary Indicator

Cross-Table Check: Lifting Fan vs Ship Type

Codebook

• E88_GEAR_TYP: ABSD_CBUB1 FLDI='35' (gearbox type, case-sensitive)

3. Per-Cursor Validations

4. J06 / Staging / Annotations

• J06: conditional, J06_AUTHOR + J06_LNCHDATE only
• Staging: ABSD_MAEM1_UPDATES
• Secondary annotation: Tablename='ABSD_MAEM1', Fieldname='E88_EFD'

5. MAEM2 and MAEM3

ABSD_MAEM2 and ABSD_MAEM3 follow the identical structure as MAEM1 but for the second and third emergency engines. Both use the dual guard pattern (TRIGGER_DISABLE + TRIGGER_DISABLE_MAEM2/MAEM3) and the same field validations with E88_ prefix fields.

Ship speed trigger. Records trial speed (E83), service speed (E84), maximum speed (E85), and unspecified speed (E86). The trigger updates ABSD_SHIP_SEARCH.SSpeed using a waterfall: service speed if available, otherwise maximum speed. Has four conditional annotation groups (one per speed type).

1. Pre-Cursor Validations

2. Per-Cursor Validations

3. ABSD_SHIP_SEARCH Maintenance

-- Service speed takes priority over maximum speed:
UPDATE ABSD_SHIP_SEARCH SET SSpeed =
  CASE WHEN ISNULL(E84_SERVICE_SPD,0) = 0
       THEN E85_MAXIMUM_SPD
       ELSE E84_SERVICE_SPD
  END
FROM ABSD_SHIP_SEARCH INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO
WHERE INSERTED.LRNO = @INDEXLRNO

Fires when E84_SERVICE_SPD or E85_MAXIMUM_SPD is updated. No SEQNO filter — updates SHIP_SEARCH regardless of which sequence number is changed.

4. Conditional Secondary Annotations (4 groups)

Unlike most MA* triggers where a secondary annotation always fires, MASP fires up to four conditional annotations. Multiple can fire in a single update if multiple speed types are changed.

5. J06 / Staging

• J06: conditional, J06_AUTHOR + J06_LNCHDATE only
• Staging: ABSD_MASP_UPDATES