10c — Triggers: Status, Flag, Class & Dimensions

Fire condition: FOR UPDATE, INSERT on ABSD_HIST — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_HIST.ABSD_HIST_Update.sql (~1,064 lines — rewrote Dec 2006, cursor-based; updated Dec 2025 to use vwABSD_OWXR_EDM)
Last significant update: Dec 2025 — MW updated to use vwABSD_OWXR_EDM (EDM-sourced OWXR data) instead of direct ABSD_OWXR table.

Field Validation Rules

Cross-Check: DOB Cannot Be in the Future for In-Service Vessels

IF (select count(*) from absd_ovge a, inserted b
    where a.lrno = b.lrno
    and b.seqno = '00'
    and b.a02_sts NOT IN ('P','O','U','F','E','N','X','Z','A')
    and a.dob > (select substring(convert(varchar, getdate(), 112),1,6))) > 0
BEGIN
    RAISERROR('Cannot change status as the Date of Build is set to a date in the future.', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
END

Statuses P, O, U, F, E, N, X, Z, A are pre-delivery/newbuilding — a future DOB is valid here. All other statuses require the DOB to be in the past.

Cross-Check: Flag Must Not Be Obsolete for Active Statuses

-- If the flag country's termination date (TDATE) is not '999912' (= currently valid),
-- the status cannot be changed to an active in-service status.
IF (SELECT Count(*) FROM Inserted
    INNER JOIN ABSD_HIFL ON Inserted.LRNO = ABSD_HIFL.LRNO AND ABSD_HIFL.SEQNO = '00'
    INNER JOIN ABSD_CBCY ON ABSD_CBCY.[KEY] = ABSD_HIFL.B04_CNTY AND ISNULL(TDATE,'') <> '999912'
    WHERE Inserted.SEQNO = '00'
      AND Inserted.A02_STS IN ('P','O','U','F','E','S','C','R','L','T','V')) > 0
BEGIN
    RAISERROR('Error FLAG is obsolete please correct the Flag before amending the status.', 16, 1) ...
END

ABSD_CBCY.TDATE = '999912' means the country/flag is still in active use. Any other TDATE (e.g., '199012') means the country code is obsolete (e.g., USSR, Yugoslavia). The vessel's flag must be corrected before it can be set to an active status.

Status Transition Cascade Actions (A02_STS, SEQNO='00')

The trigger executes the following actions when A02_STS changes on the current record. All run inside the cursor loop per LRNO+SEQNO combination:

IF (SELECT COUNT(*) FROM ABSD_OVSMC WHERE LRNO = @CURR_RECORD AND SEQNO='00') = 0
   AND (SELECT A02_STS FROM inserted WHERE LRNO = @CURR_RECORD AND SEQNO = @CURR_SEQ) = 'S'
    INSERT INTO ABSD_OVSMC (...) VALUES (@CURR_RECORD,'00','','','','Unknown','','','','','',GETDATE(),NULL,'9991001','LRF',NULL)

IF (SELECT COUNT(*) FROM ABSD_OVDOC WHERE LRNO = @CURR_RECORD AND SEQNO='00') = 0
   AND (SELECT A02_STS FROM inserted WHERE LRNO = @CURR_RECORD AND SEQNO = @CURR_SEQ) = 'S'
    INSERT INTO ABSD_OVDOC (...) VALUES (@CURR_RECORD,'00','9991001',GETDATE(),'LRF',NULL)

-- Delivery within 2 months of DOB
AND GE.DOB > LEFT(DBO.FNREALDATETOLRDATE(DATEADD(M,-2,GETDATE())),6)

UPDATE SUPPLEMENTAL_ABSD_NCON
SET RELEASEDATE = REPLICATE('0',2-LEN(DAY(GETDATE()))) + CAST(DAY(GETDATE()) AS VARCHAR(2))
    + ':' + REPLICATE('0',2-LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS VARCHAR(2))
    + ':' + RIGHT(YEAR(GETDATE()),2)
WHERE LRNO = @CURR_RECORD

SELECT @STS = (SELECT A02_STS FROM ABSD_HIST WHERE SEQNO='00' AND LRNO = @CURR_RECORD)
UPDATE ABSD_OVNA SET SHP_ACT_ST = @STS WHERE LRNO = @CURR_RECORD AND SEQ_NO = '00'

IF (select count(*) from absd_math a, absd_hist b where a.lrno = @CURR_RECORD and a.lrno = b.lrno
    and b.seqno = '00' and a02_sts NOT IN ('9','B','G','H','V') and e95_thst_no > 0) > 0
    UPDATE absd_math SET e95_thst_stat = (SELECT a02_sts FROM absd_hist WHERE lrno = @CURR_RECORD AND seqno = '00')
    WHERE lrno = @CURR_RECORD

UPDATE absd_hige SET e45_gs = (SELECT a02_sts FROM absd_hist WHERE lrno = @CURR_RECORD AND seqno = '00')
WHERE lrno = @CURR_RECORD AND seqno = '00'

IF (select count(*) from absd_himo a, absd_hist b where a.lrno = @CURR_RECORD
    and a.lrno = b.lrno and a.seqno = '00' and b.seqno = '00' and es <> a02_sts) > 0
    UPDATE absd_himo SET es = (SELECT a02_sts FROM absd_hist WHERE lrno = @CURR_RECORD AND seqno = '00')
    WHERE lrno = @CURR_RECORD

-- Example for status D:
IF (SELECT COUNT(*) FROM ABSD_OVCA WHERE LRNO = @CURR_RECORD
    AND F14_EFD = '000000' AND F14_NOTES IS NULL AND SEQ_NO = '01') = 1
    UPDATE absd_ovca SET F14_EFD = CONVERT(VARCHAR(6),GETDATE(),12), F14_NOTES = 'BROKEN UP'
    WHERE lrno = @CURR_RECORD AND seq_no = '01'
ELSE IF (SELECT COUNT(*) FROM ABSD_OVCA WHERE LRNO = @CURR_RECORD
    AND F14_EFD <> '000000' AND F14_NOTES IS NOT NULL) >= 1
    EXEC spMakeNewCas @CURR_RECORD
    UPDATE absd_ovca SET F14_EFD = CONVERT(VARCHAR(6),GETDATE(),12), F14_NOTES = 'BROKEN UP'
    WHERE lrno = @CURR_RECORD AND seq_no = '01'

DELETE FROM ABSD_OWNC WHERE SHLRNO = @CURR_RECORD

-- Link registered owner (RC = Registered Owner)
INSERT ABSD_OWNC (OWCODE, SHLRNO, REL1, REL2, PI, VER)
SELECT H01_OWNER_CODE, A.LRNO, 'RC', NULL, 'P', 'C'
FROM ABSD_HIOW A, ABSD_HIST B
WHERE A.LRNO = B.LRNO AND B.SEQNO = '00' AND A.LRNO = @CURR_RECORD
  AND H01_OWNER_CODE <> '9991001'
  AND A02_STS IN ('P','O','U','A','F','E') AND A.SEQNO = '00'

-- Link ship manager (MR = Ship Manager)
INSERT ABSD_OWNC (OWCODE, SHLRNO, REL1, REL2, PI, VER)
SELECT H02_MANAGER, A.LRNO, 'MR', NULL, 'P', 'C'
FROM ABSD_HIMA A, ABSD_HIST B
WHERE A.LRNO = B.LRNO AND B.SEQNO = '00' AND A.LRNO = @CURR_RECORD
  AND H02_MANAGER <> '9991001'
  AND A02_STS IN ('P','O','U','A','F','E') AND A.SEQNO = '00'

-- Update pattern: delete and re-insert OWST record
DELETE FROM ABSD_OWST WHERE OWCODE = @OWCODE
IF (SELECT @OWNCOUNT + @MANCOUNT + @IRP_AGENTFOR + @RP_AGENTFOR + @P_AGENTFOR) > 0
    INSERT INTO ABSD_OWST(OWCODE, IRP_OWNED, IRP_MANAGED, IRP_AGENTFOR, RP_AGENTFOR, P_AGENTFOR)
    VALUES (@OWCODE, @OWNCOUNT, @MANCOUNT, @IRP_AGENTFOR, @RP_AGENTFOR, @P_AGENTFOR)

SET @QUARTER = CASE WHEN SUBSTRING(A02_EFD,5,2) IN ('01','02','03','99') THEN 'M'
                    WHEN SUBSTRING(A02_EFD,5,2) IN ('04','05','06') THEN 'J'
                    WHEN SUBSTRING(A02_EFD,5,2) IN ('07','08','09') THEN 'S'
                    ELSE 'D' END + SUBSTRING(A02_EFD,3,2)

IF (SELECT A02_STS FROM inserted i WHERE i.SEQNO = '00' AND i.LRNO = @CURR_RECORD) = 'X'
    UPDATE SUPPLEMENTAL_ABSD_NCON SET NBEmailIndicator = '1' WHERE LRNO = @CURR_RECORD

J06 Audit (Conditional)

ABSD_HIST uses a conditional J06 update — only writes if J06 has not already been written for this user/date/LRNO combination:

IF NOT (SELECT Count(*) FROM ABSD_OVGE
    WHERE J06_AUTHOR = UPPER(LEFT(RIGHT(system_user,...),3))
      AND J06_LNCHDATE = ...
      AND LRNO = @CURR_RECORD) > 0
    UPDATE ABSD_OVGE SET J06_AUTHOR=..., J06_LNCHDATE=... WHERE LRNO = @CURR_RECORD

Staging (Full History Snapshot)

-- Note: copies ALL HIST rows for this LRNO, not just the updated row
DELETE FROM ABSD_HIST_UPDATES WHERE LRNO = @CURR_RECORD
INSERT ABSD_HIST_UPDATES SELECT * FROM ABSD_HIST WHERE LRNO = @CURR_RECORD

ABSD_SHIP_SEARCH Update

-- SEQNO='00' only
IF UPDATE(A02_STS)
    UPDATE ABSD_SHIP_SEARCH
    SET STATUSCODE = INSERTED.A02_STS,
        STATUS = ABSD_CBUB1.TRUD    -- decoded status description
    FROM ABSD_SHIP_SEARCH
        INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO AND INSERTED.SEQNO = '00'
        INNER JOIN ABSD_CBUB1 ON INSERTED.A02_STS COLLATE SQL_Latin1_General_CP1_CS_AS = ABSD_CBUB1.CODE
            AND ABSD_CBUB1.FLDI = '19'

Primary Annotations (tblChanges)

Secondary Annotation Groups

Fire condition: FOR UPDATE, INSERT on ABSD_HIFL — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_HIFL.ABSD_HIFL_Update.sql (~299 lines)
Last significant update: March 2026 (MM) — replaced DBO.ABSD_CBCY with EDM.dbo.T_REF_COUNTRY for flag validation.

Field Validation Rules

-- Country validation (updated March 2026)
IF UPDATE(B04_CNTY)
BEGIN
    IF NOT (SELECT COUNT(*) FROM ABSD_HIFL
        INNER JOIN EDM.dbo.T_REF_COUNTRY C ON ABSD_HIFL.B04_CNTY = C.COUNTRY_CODE COLLATE Latin1_General_CI_AI
        WHERE LRNO IN (SELECT LRNO FROM INSERTED) AND SEQNO IN (SELECT SEQNO FROM INSERTED)) > 0
    BEGIN
        RAISERROR('The code in B04_CNTY does not match a record in table CBCY', 16, 1) ...
    END
END

-- Port validation (country+town pair)
IF UPDATE(B04_CNTY) OR UPDATE(B04_TOWN)
BEGIN
    IF NOT (SELECT COUNT(*) FROM ABSD_HIFL
        INNER JOIN ABSD_CBTO1 ON ABSD_HIFL.B04_CNTY = ABSD_CBTO1.CNTRY AND ABSD_HIFL.B04_TOWN = ABSD_CBTO1.TOWN
        WHERE LRNO IN (SELECT LRNO FROM INSERTED) AND SEQNO IN (SELECT SEQNO FROM INSERTED)) > 0
    BEGIN
        RAISERROR('The code in B04_TOWN does not match a record in table CBTO1', 16, 1) ...
    END
END

J06 Audit

UPDATE ABSD_OVGE SET J06_AUTHOR=..., J06_LNCHDATE=... WHERE LRNO IN (SELECT LRNO FROM inserted)

Cascade 1: ABSD_OVNA.FLAG_CDE Sync (SEQNO='00' only)

IF (SELECT COUNT(*) FROM inserted WHERE SEQNO = '00') > 0
BEGIN
    SELECT @FLAG = (SELECT B04_CNTY FROM ABSD_HIFL WHERE SEQNO='00' AND LRNO = @LRNO)
    UPDATE ABSD_OVNA SET FLAG_CDE = @FLAG WHERE LRNO = @LRNO AND SEQ_NO = '00'
END

Cascade 2: EFD Propagation to ABSD_OVGE and SUPPLEMENTAL_ABSD_OVGE

When the flag country OR the flag EFD changes (either field), the corresponding EFD is propagated to all registration EFD fields on ABSD_OVGE, and to MMSI_EFD on SUPPLEMENTAL_ABSD_OVGE. This ensures that when a vessel is re-flagged, all EFD fields reflect the new flag date:

IF UPDATE(B04_CNTY) OR UPDATE(B04_EFD)
BEGIN
    SELECT @EFD = (SELECT B04_EFD FROM ABSD_HIFL WHERE SEQNO='00' AND LRNO = @LRNOefd)
    UPDATE ABSD_OVGE
    SET B01_EFD  = @EFD,   -- Callsign EFD
        B02_EFD  = @EFD,   -- Official number EFD
        B10_EFD  = @EFD,   -- Fishing number EFD
        B11_EFD  = @EFD    -- SATCOM EFD
    WHERE LRNO = @LRNOefd
    UPDATE SUPPLEMENTAL_ABSD_OVGE SET MMSI_EFD = @EFD WHERE LRNO = @LRNOefd
END

Cascade 3: Registration Field Nulling on Flag Change

When B04_CNTY (the flag country) changes, all registration-specific identifiers are automatically cleared. This is because each identifier (callsign, official number, fishing number, MMSI, SATCOM) is flag-specific — when the vessel re-flags, all old registration numbers become invalid:

IF UPDATE(B04_CNTY)
BEGIN
    -- Clear ABSD_OVGE registration fields
    UPDATE ABSD_OVGE SET B11_SATCOM       = NULL WHERE LRNO = @LRNO
    UPDATE ABSD_OVGE SET B01_CALLSIGN     = NULL WHERE LRNO = @LRNO
    UPDATE ABSD_OVGE SET B11_ANSBK_CODE   = NULL WHERE LRNO = @LRNO
    UPDATE ABSD_OVGE SET B02_OFFNO        = NULL WHERE LRNO = @LRNO
    UPDATE ABSD_OVGE SET B10_FISHNO       = NULL WHERE LRNO = @LRNO
    -- Clear SUPPLEMENTAL_ABSD_OVGE extended fields
    UPDATE SUPPLEMENTAL_ABSD_OVGE SET FISHNO20  = NULL WHERE LRNO = @LRNO
    UPDATE SUPPLEMENTAL_ABSD_OVGE SET MMSI      = NULL WHERE LRNO = @LRNO
    UPDATE SUPPLEMENTAL_ABSD_OVGE SET MMSI_EFD  = NULL WHERE LRNO = @LRNO
    -- Clear SUPPLEMENTAL_ABSD_HIFL extended fields (current record only)
    UPDATE SUPPLEMENTAL_ABSD_HIFL SET MMSI      = NULL WHERE LRNO = @LRNO AND SEQNO = '00'
    UPDATE SUPPLEMENTAL_ABSD_HIFL SET CALLSIGN  = NULL WHERE LRNO = @LRNO AND SEQNO = '00'
END

Staging

DELETE FROM ABSD_HIFL_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_HIFL_UPDATES SELECT * FROM ABSD_HIFL WHERE LRNO IN (SELECT LRNO FROM inserted)

ABSD_SHIP_SEARCH Update (cursor-based, SEQNO='00' only)

IF UPDATE(B04_CNTY) OR UPDATE(B04_TOWN)
    UPDATE ABSD_SHIP_SEARCH
    SET FLAG     = INSERTED.B04_CNTY,
        PORT     = INSERTED.B04_TOWN,
        PORTNAME = VWPORTDECODE.TOWNNAME,
        FLAGNAME = VWCOUNTRYDECODE.ALLNAMES
    FROM ABSD_SHIP_SEARCH
        INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO AND INSERTED.SEQNO = '00'
        LEFT JOIN VWPORTDECODE ON INSERTED.B04_TOWN = VWPORTDECODE.TOWN AND INSERTED.B04_CNTY = VWPORTDECODE.CNTRY
        LEFT JOIN VWCOUNTRYDECODE ON INSERTED.B04_CNTY = VWCOUNTRYDECODE.[KEY] COLLATE Latin1_General_CI_AI

Primary Annotations (tblChanges)

Secondary Annotation Groups

Fire condition: FOR UPDATE, INSERT on ABSD_HILC — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_HILC.ABSD_HILC_Update.sql (~241 lines)

Field Validation Rules

J06 Audit + Staging

UPDATE ABSD_OVGE SET J06_AUTHOR=..., J06_LNCHDATE=... WHERE LRNO IN (SELECT LRNO FROM inserted)

DELETE FROM ABSD_HILC_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_HILC_UPDATES SELECT * FROM ABSD_HILC WHERE LRNO IN (SELECT LRNO FROM inserted)

ABSD_SHIP_SEARCH — Dual Update on LRCL_IND Change (SEQNO='00')

Two separate SHIP_SEARCH updates fire when LRCL_IND changes on SEQNO='00':

-- Update 1: classsearch via vwClass view
UPDATE ABSD_SHIP_SEARCH
SET classsearch = vwClass.classsearch
FROM ABSD_SHIP_SEARCH
    INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO AND INSERTED.SEQNO = '00'
    INNER JOIN vwClass ON INSERTED.lrno = vwClass.lrno

-- Update 2: ClassList via fnclasslist() function
UPDATE ABSD_SHIP_SEARCH
SET ClassList = LTRIM(dbo.fnclasslist(@INDEXLRNO))
FROM ABSD_SHIP_SEARCH WITH (NOLOCK)
    INNER JOIN ABSD_HIST WITH (NOLOCK) ON ABSD_SHIP_SEARCH.LRNO = ABSD_HIST.LRNO AND ABSD_HIST.SEQNO = '00'
WHERE ISNULL(ABSD_SHIP_SEARCH.ClassList,'') <> ISNULL(LTRIM(dbo.fnclasslist(ABSD_SHIP_SEARCH.LRNO)),'')
  AND ABSD_SHIP_SEARCH.LRNO = @INDEXLRNO

Business Rule: Class Notation Mining (spClassNotationMiningAdd / Delete)

When the LR classification status changes on SEQNO='00', the trigger manages the class notation mining records via stored procedures. These extract and index class notation data from the free-text LRHN records:

SELECT @CLASS_NOTATION = (SELECT F03_NOT_LINES FROM ABSD_LRHN WHERE LRNO = @INDEXLRNO)
SELECT @CLASS_STATUS = (SELECT LRCL_IND FROM INSERTED WHERE LRNO = @INDEXLRNO AND SEQNO='00')

IF @CLASS_STATUS = 'W'  -- Declassed
    EXEC spClassNotationMiningDelete @INDEXLRNO, '00', 'LR', ''

IF @CLASS_STATUS = 'Y'  -- Reclassed
    EXEC spClassNotationMiningAdd @INDEXLRNO, '00', 'LR', @CLASS_NOTATION

F03_NOT_LINES from ABSD_LRHN contains the LR class notation text (e.g., "+100A1 *IWS LMC"). The mining SPs parse this text into structured notation records.

Primary Annotations (tblChanges)

Secondary Annotation Groups

Fire condition: FOR UPDATE, INSERT on ABSD_HILE — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_HILE.ABSD_HILE_Update.sql (~798 lines — old commented-out version preserved below active code)
Key rewrite: Nov 2007 (MRE) — added cursor to allow bulk updates. Complex cross-checks moved from inline SQL into spValidateLengths.

Field Validation Rules (Active Code)

spValidateLengths — Rules Enforced

The spValidateLengths SP (documented in Section 12a) enforces the length hierarchy:

• LOA ≥ LBP ≥ REG — no length dimension may exceed the overall LOA
• Length comparisons against breadth tables (HIBR), depth tables (HIDE), draught tables (HIDR)
• Compartment sum checks (sum of FUCO2 compartment lengths in position 39)
• Hatch length sum checks (sum of FUHA2 hatch lengths)

spValidateC02_BBOW — Bulbous Bow Type Restriction

The bulbous bow indicator (C02_BBOW) was formerly restricted to only tank and bulk carrier types (D01_INT_BASIC starting with '1' or '5'). This logic is now enforced by spValidateC02_BBOW. The old inline rule was:

/* From commented-out old code — now in spValidateC02_BBOW:
IF C02_BBOW = 'Y' AND D01_INT_BASIC NOT LIKE '1%' AND D01_INT_BASIC NOT LIKE '5%'
    RAISERROR('C02_BBOW cannot be Y unless D01_INT_BASIC starts with a 1 or 5', 16, 1)
*/

J06 Audit + Staging (Inside Cursor Loop)

-- Per LRNO+SEQNO inside the cursor
UPDATE ABSD_OVGE SET J06_AUTHOR=..., J06_LNCHDATE=... WHERE LRNO = @INDEXLRNO

DELETE FROM ABSD_HILE_UPDATES WHERE LRNO = @INDEXLRNO
INSERT ABSD_HILE_UPDATES SELECT * FROM ABSD_HILE WHERE LRNO = @INDEXLRNO

ABSD_SHIP_SEARCH — LOA Priority Waterfall

SHIP_SEARCH.LOA is set using a priority waterfall: use LOA if > 0, otherwise fall back to LBP, otherwise REG:

IF UPDATE(C02_LOA) OR UPDATE(C02_LBP) OR UPDATE(C02_REG)
BEGIN
    UPDATE ABSD_SHIP_SEARCH
    SET LOA = CASE WHEN INSERTED.C02_LOA > 0 THEN INSERTED.C02_LOA
                   WHEN INSERTED.C02_LBP > 0 THEN INSERTED.C02_LBP
                   ELSE INSERTED.C02_REG
              END
    FROM ABSD_SHIP_SEARCH
        INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO AND INSERTED.LRNO = @INDEXLRNO
END

Primary Annotations (tblChanges)

Secondary Annotation Groups

Dimension Field Reference