10d – Ownership & Tonnage Triggers

The registered owner history trigger is the most complex trigger in the database. It validates EFDs, prevents duplicate/out-of-sequence owner records, maintains three-position OWSH relationship slots, auto-derives the beneficial ownership chain into HIPP/HITP, and recomputes OWST fleet statistics for both old and new owners.

1. Guard & Execution Mode

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE)
BEGIN
  SET NOCOUNT ON
  DECLARE INDEX_cursor INSENSITIVE CURSOR FOR SELECT LRNO, SEQNO FROM inserted
  -- per-row processing via cursor
END

Uses a cursor over all (LRNO, SEQNO) pairs in inserted to handle bulk imports correctly. All business logic executes inside the cursor loop.

2. Field Validation

3. Duplicate & Sequence Prevention

3a. Same Owner Consecutive Duplicate

• If H01_OWNER_CODE changed: checks whether the new owner is identical to the adjacent historical record (self-join on LRNO where SEQNOs are consecutive). Prevents inserting a new historical record with the same owner as its immediate neighbour.
• Error: "YOU HAVE CREATED A DUPLICATE ENTRY IN THE OWNER HISTORY - PLEASE CORRECT IMMEDIATELY"

3b. EFD Out-of-Sequence Check

• Applied when H01_EFD is updated. Compares new EFD against the EFD of the next higher SEQNO (upper bound) and the next lower SEQNO (lower bound).
• Uses the normalisation formula: LEFT(EFD,4) + REPLACE(REPLACE(SUBSTRING(EFD,5,4),'0000','0001'),'9999','0000') to allow sentinel values 0000 and 9999 at the ends of the sequence.
• If EFD falls outside the range between adjacent SEQNOs → RAISERROR + ROLLBACK.
• Error: "The Owner effective dates are out of sequence please correct immediately."

3c. Same Owner + Same EFD Across SEQNOs

• When either H01_EFD or H01_OWNER_CODE is updated: checks for another SEQNO on the same vessel with identical owner code AND identical EFD.
• Error: "There is another record against this vessel with the same Owner and Effective Date please correct immediately."

4. Owner = Manager Auto-Fix (Cross-Table Cascade)

-- Step 1: Insert a per-session guard so HIMA's own trigger doesn't fire recursively
INSERT INTO TRIGGER_DISABLE_HIMA (spid) VALUES (@@spid)

-- Step 2: Move current HIMA record to history
EXEC spMakehimaHistorical @CURR_RECORD

-- Step 3: Set current manager to "no manager" sentinel
UPDATE ABSD_HIMA SET H02_MANAGER = '9991001' WHERE LRNO=@CURR_RECORD AND SEQNO='00'

-- Step 4: Remove the per-session guard
DELETE FROM TRIGGER_DISABLE_HIMA WHERE spid = @@spid

Variant: Old Owner = ISM Ship Manager

• If the old owner (from deleted) matches the current ABSD_HISM.SHIPMANAGER: calls spMakehimaHistorical and then sets ABSD_HIMA.H02_MANAGER to the HISM SHIPMANAGER value (promotes the ISM ship manager to become the HIMA manager).

5. OWSH Relationship Maintenance (3-Position Slots)

ABSD_OWSH is the owner ship holdings table. It maintains three relationship position slots (REL1_1, REL1_2, REL1_3) for each owner-vessel pairing.

5a. New Owner Record (Addition)

• Retrieves the owner's current relationship fields from OWSH: REL1_1, REL1_2, REL1_3
• If LRNO already exists in one of the three positions → no-op (already linked)
• If all three positions are empty → inserts new OWSH row with LRNO in REL1_1, shifts others
• Shift-down logic: If REL1_1 is occupied, shifts REL1_1→REL1_2, REL1_2→REL1_3, places new LRNO in REL1_1
• If REL1_3 would be overflowed → the oldest reference (REL1_3) is silently dropped

5b. Old Owner Record (Removal)

• For the owner code in deleted, removes the vessel's LRNO from whichever REL1_x slot contains it
• Compacts the remaining slots upward (REL1_2→REL1_1, REL1_3→REL1_2)
• Sets vacated slot to NULL

6. Beneficial Ownership Auto-Derivation (HIPP / HITP)

View Dependencies (Updated Dec 2025 / Feb 2026)

• vwABSD_OWXR_EDM (updated Dec 2025) — owner cross-reference relationships from EDM; replaces direct ABSD_OWXR table to avoid stale data
• vwABSD_OWGE_EDM (updated Feb 2026) — owner general data from EDM; replaces direct ABSD_OWGE table

Algorithm

-- Level 1: Start with H01_OWNER_CODE from inserted
SET @L1_OWNER = (SELECT H01_OWNER_CODE FROM inserted WHERE SEQNO='00')

-- Level 2: Traverse OWXR for parent relationship
SET @L2_OWNER = (SELECT OWCODE_PARENT FROM vwABSD_OWXR_EDM WHERE OWCODE_CHILD=@L1_OWNER)

-- Level 3: Traverse again
SET @L3_OWNER = (SELECT OWCODE_PARENT FROM vwABSD_OWXR_EDM WHERE OWCODE_CHILD=@L2_OWNER)

-- Beneficial Owner = deepest non-null level that has GROUP_OWGE='A' (active)
--                   AND CONF IN ('N', NULL) (not confidential)
--                   AND PUBLIND_1 IS set (publicly listed indicator)

HIPP / HITP / HIPP_DATED Updates

• ABSD_HIPP: Upsert — if SEQNO='00' row exists: UPDATE; else INSERT — sets H03_PPC to derived beneficial owner code
• ABSD_HITP: Upsert — sets H04_TPC to derived technical manager code
• ABSD_HIPP_DATED: INSERT new dated record capturing the BO at time of owner change

7. OWNC Rebuild

• Called for both new owner (from inserted) and old owner (from deleted)
• Rebuilds the ABSD_OWNC newcon cross-link for each affected owner code
• Only processes vessels with ABSD_HIST.A02_STS IN ('P','O','U','A','F','E') (active statuses)
• OWNC role: RC (registered owner) set from HIOW owner code

8. OWST Fleet Statistics Recompute

• Called for BOTH the old owner code (from deleted.H01_OWNER_CODE) and the new owner code (from inserted.H01_OWNER_CODE)
• Uses vwABSD_OWXR_EDM to compute ownership group membership
• Recomputes: IRP_OWNED, IRP_MANAGED, IRP_AGENTFOR, RP_AGENTFOR, P_AGENTFOR in ABSD_OWST

9. J06 Audit Stamp

UPDATE ABSD_OVGE SET
  J06_AUTHOR   = LEFT(@Initials, 3),
  J06_LNCHDATE = YYMMDD formula,
  J06_LNCHTIME = HHMMSS formula
WHERE LRNO = inserted.LRNO

10. ABSD_SHIP_SEARCH Maintenance

11. Annotations

The ship manager / bareboat charterer history trigger. Uses a two-level guard to prevent re-entrant firing when called programmatically from the HIOW trigger. Maintains the OWSH manager relationship slots and rebuilds OWNC/OWST on manager changes.

1. Double Guard Architecture

-- Outer guard: per-session lock inserted by HIOW before calling spMakehimaHistorical
IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE_HIMA WHERE spid = @@spid)
BEGIN
  -- Inner guard: global trigger disable
  IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE)
  BEGIN
    -- all logic here
  END
END

2. Field Validation

3. Manager ≠ Owner Cross-Check

• If H02_MANAGER is updated, checks whether the new manager code matches the current registered owner (ABSD_HIOW WHERE SEQNO='00').
• Error: "The Ship Manager cannot be the same as the current Owner" → RAISERROR + ROLLBACK
• Exception: sentinel code 9991001 (no manager) is exempt from this check.

4. Duplicate & Sequence Prevention

• Same H02_MANAGER consecutive SEQNOs: self-join detects adjacent rows with identical manager → RAISERROR
• EFD out-of-sequence: same normalisation formula as HIOW (0000/9999 sentinels handled); upper bound uses sentinel SEQNO '95' as max
• Same manager + same EFD on different SEQNO: RAISERROR

5. OWSH Manager Relationship Maintenance (MR Slots)

• Works identically to HIOW's RC slot maintenance but for the manager relationship type (MR)
• New manager: locates or creates OWSH row for new manager, places LRNO in REL1_1 (shifting REL1_1→REL1_2→REL1_3 as needed)
• Old manager: removes LRNO from OWSH row for old manager, compacts slots

6. OWNC Rebuild

• Rebuilds ABSD_OWNC for both the old manager code (deleted) and new manager code (inserted)
• OWNC role: MR (manager) set from HIMA manager code
• Only for active vessel statuses: A02_STS IN ('P','O','U','A','F','E')

7. OWST Fleet Statistics Recompute

• Recomputes OWST for both the old manager (from deleted.H02_MANAGER) and new manager (from inserted.H02_MANAGER)
• Uses vwABSD_OWXR_EDM for group membership traversal

8. J06 Audit Stamp

Updates ABSD_OVGE.J06_AUTHOR, J06_LNCHDATE, J06_LNCHTIME for all rows in inserted.

9. Annotations

ISM Ship Manager history trigger. Maintains a parallel management track specifically for the ISM-designated ship manager. When the SHIPMANAGER changes, automatically synchronises with ABSD_HIMA to keep the management chain consistent.

1. Guard Architecture

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

Outer guard is the global TRIGGER_DISABLE. Inner guard is TRIGGER_DISABLE_NEWOOM (new-OOM suppression — used during bulk new owner-operator-manager operations).

2. Field Validation

3. Duplicate & Sequence Prevention

3a. Consecutive Duplicate SHIPMANAGER

• Self-join: checks whether the new SHIPMANAGER equals the adjacent record's SHIPMANAGER (consecutive SEQNO without EFD check — EFD check commented out)
• Error: "YOU HAVE CREATED A DUPLICATE ENTRY IN THE SHIP MANAGER HISTORY - PLEASE CORRECT IMMEDIATELY"

3b. EFD Out-of-Sequence

• Same 0000/9999 sentinel normalisation as HIOW/HIMA
• Upper bound sentinel: SEQNO '95' (not '00') — HISM uses '95' as the effective "top of history" SEQNO boundary
• Error: "The Ship Manager effective dates are out of sequence please correct immediately."

3c. Same SHIPMANAGER + Same EFD Across SEQNOs

• Error: "There is another record against this vessel with the same Ship Manager and Effective Date please correct immediately."

4. HIMA Auto-Synchronisation

The cursor processes only SEQNO='00' rows. For each current HISM record, it performs the following logic:

EFD Sync (Regardless of Manager Change)

• If SHIPMANAGER matches HIMA.H02_MANAGER AND HISM.EFD ≠ HIMA.H02_EFD: updates HIMA.H02_EFD to match HISM.EFD for SEQNO='00'
• Precondition: SHIPMANAGER ≠ '9991001'

5. Commented-Out Rules (Preserved for Context)

• GROUP_OWGE check: commented-out rule prevented managers with GROUP_OWGE IN ('C','D','I') (dead/inactive/personnel) from being assigned to active vessels
• Obsolete codes list: commented-out rule blocked: 9997500, 9992509, 9995154, 9991001, 9991284, 9992000

6. ABSD_SHIP_SEARCH Maintenance

7. J06 Audit Stamp

Updates ABSD_OVGE.J06_AUTHOR, J06_LNCHDATE, J06_LNCHTIME for all rows in inserted.

8. Annotations

Operator history trigger. Records the party responsible for the commercial operation of the vessel. Note that this trigger checks TRIGGER_DISABLE_NEWOOM only — it does not check the global TRIGGER_DISABLE table. Consequently, it fires during new-OOM operations only if TRIGGER_DISABLE_NEWOOM is populated.

1. Field Validation

2. Duplicate & Sequence Prevention

2a. Consecutive Duplicate OPERATOR_CODE+EFD

• Self-join: checks whether the new OPERATOR_CODE+OPERATOR_EFD combo equals the adjacent consecutive record
• Error: "YOU HAVE CREATED A DUPLICATE ENTRY IN THE OPERATOR HISTORY - PLEASE CORRECT IMMEDIATELY"

2b. EFD Out-of-Sequence

• Same 0000/9999 sentinel normalisation formula; upper bound sentinel SEQNO='95'
• Error: "The Operator effective dates are out of sequence please correct immediately."

2c. Same OPERATOR_CODE + Same EFD

• Error: "There is another record against this vessel with the same Operator and Effective Date please correct immediately."

3. Obsolete Code Enforcement

• Blocks OPERATOR_CODE IN ('9995154','9991001','9991284','9999996') when the vessel is in a terminal status
• Terminal statuses: ABSD_HIST.A02_STS IN ('S','C','R','L','T') (scrapped, cancelled, laid-up, etc.)
• Error: "This OPERATOR Code is not to be used"
• Note: The broader GROUP_OWGE restriction (blocked dead/inactive companies) is commented out

4. J06 Audit Stamp

Uses SYSTEM_USER directly (not @Initials from spGetUserSettings): UPPER(LEFT(RIGHT(SYSTEM_USER, LEN(SYSTEM_USER)-CHARINDEX('\',SYSTEM_USER)),3)). Updates J06_AUTHOR and J06_LNCHDATE only (no J06_LNCHTIME).

5. ABSD_SHIP_SEARCH Maintenance

6. Annotations

Group beneficial owner trigger. Records the group-level beneficial owner for a vessel.

1. Processing Architecture

Cursor-based, iterating over all (LRNO, SEQNO) pairs in inserted. No field validation is performed.

2. Annotation Logic — GBO_CODE

• Fires only when GBO_CODE is updated AND the new value differs from the deleted value (anti-noise filter: COUNT(*) WHERE i.GBO_CODE = d.GBO_CODE <> 1)
• ChangeType is hardcoded to 'Group Owner' (does NOT call spGetEnglishFieldName)
• OldValue special handling: When @IsNew=1 (new record), OldValue is set to i.GBO_CODE (same as NewValue) rather than NULL. This differs from the standard pattern where OldValue would be NULL for new inserts.
• No Notes field; no secondary annotation.

3. J06 Audit Stamp

Updates ABSD_OVGE.J06_AUTHOR, J06_LNCHDATE, J06_LNCHTIME using @Initials from spGetUserSettings.

4. No SHIP_SEARCH Maintenance

ABSD_HIGBO does not contribute to ABSD_SHIP_SEARCH columns.

Beneficial principal trigger. ABSD_HIPP is primarily maintained by the HIOW trigger (via the 3-level BO chain derivation). This trigger validates manually-entered updates and maintains the J06 audit stamp.

1. Field Validation

2. J06 Audit Stamp

J06_AUTHOR + J06_LNCHDATE only (no J06_LNCHTIME). Uses SYSTEM_USER directly (not spGetUserSettings).

3. Annotations

4. No SHIP_SEARCH Maintenance

ABSD_HIPP does not contribute to ABSD_SHIP_SEARCH columns.

Technical principal trigger. Like HIPP, ABSD_HITP is primarily auto-derived by the HIOW trigger. This trigger validates manual edits and maintains the staging table and J06 audit.

1. Field Validation

2. Staging Table Maintenance

IF EXISTS (SELECT * FROM ABSD_HITP_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted))
  DELETE FROM ABSD_HITP_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_HITP_UPDATES SELECT * FROM ABSD_HITP WHERE LRNO IN (SELECT LRNO FROM inserted)

The staging table ABSD_HITP_UPDATES is refreshed with the full set of current HITP rows for each affected LRNO (not just the inserted row).

3. J06 Audit Stamp

J06_AUTHOR + J06_LNCHDATE only (no J06_LNCHTIME). Uses SYSTEM_USER directly.

4. Annotations

Lloyd's tonnage trigger. Validates gross tonnage (GT) and net tonnage (NT) measurements, enforces GT≥NT (with a ship-type exception for A37A2PC containers), syncs GRT_L to ABSD_OVNA, maintains SHIP_SEARCH, and feeds the staging table.

1. Field Validation

2. GT > NT Cross-Check

Exception: STAT5CODE = 'A37A2PC' (Container Vessels)

-- Rule removed 23/04/2024 (issue 54060):
--   "Remove the rule stopping a user to enter a higher NT value than GT for STAT5CODE='A37A2PC'."
-- Implementation: check ABSD_OVTY + tblMasterShipTypeXref
-- If vessel's shiptype maps to STAT5CODE='A37A2PC' → skip GT>NT check entirely
-- EFD date filter on OVTY also removed 28/05/2024 at Mayank's request

The exception is implemented for both UPDATE(B07_GROSS) and UPDATE(B07_NET) branches.

3. GRT_L Cascade to ABSD_OVNA

-- For SEQNO='00' only:
SELECT @GROSS = (SELECT B07_GROSS FROM ABSD_HITL WHERE SEQNO='00' AND LRNO=@INDEXLRNO)
UPDATE ABSD_OVNA SET GRT_L = @GROSS, INV_GRT_L = @GROSS * -1 WHERE LRNO=@INDEXLRNO AND SEQ_NO='00'

ABSD_OVNA.GRT_L (Lloyd's gross register tonnage) and its inverse INV_GRT_L are kept in sync with HITL. The inverse is used in some aggregation queries.

4. Staging Table Maintenance

IF EXISTS (SELECT * FROM ABSD_HITL_UPDATES WHERE LRNO = @INDEXLRNO)
  DELETE FROM ABSD_HITL_UPDATES WHERE LRNO = @INDEXLRNO
INSERT ABSD_HITL_UPDATES SELECT * FROM inserted WHERE LRNO = @INDEXLRNO

Unlike HITP which takes from the base table, HITL_UPDATES is populated from the inserted pseudo-table.

5. J06 Audit Stamp

J06_AUTHOR + J06_LNCHDATE only (no J06_LNCHTIME). Uses SYSTEM_USER directly. Run inside the cursor loop, updated per-LRNO.

6. ABSD_SHIP_SEARCH Maintenance

7. Annotations

Deadweight and draught measurements trigger. Validates DWT/draught values, enforces DWT ≤ displacement cross-check via ABSD_STGE, maintains SHIP_SEARCH, and fires dual secondary annotation groups.

1. Field Validation

2. Cross-Field Ordering Rules

• DWTS ≤ DWTL: Summer DWT must not exceed loaded DWT. Error: "C07_DWTS cannot be greater than C07_DWTL"
• DS ≤ DL: Summer draught must not exceed loaded draught. Error: "C07_DS cannot be greater than C07_DL"

3. Displacement Cross-Check

4. ABSD_SHIP_SEARCH Maintenance

Note: SHIP_SEARCH update in HIDR does NOT filter on SEQNO — it updates for any SEQNO where C07_DWTL or C07_DL has changed. Only SEQNO='00' rows from inserted join to SHIP_SEARCH (via the INNER JOIN), but no explicit SEQNO filter is applied in the WHERE clause before the join.

5. J06 Audit Stamp

J06_AUTHOR + J06_LNCHDATE only (no J06_LNCHTIME). Run inside cursor loop per-LRNO.

6. Annotations

Two secondary annotation groups are written for every HIDR update — the only trigger in this family with dual secondary groups.