10b — Triggers: Vessel Identity

Fire condition: FOR UPDATE, INSERT on ABSD_OVGE — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVGE.ABSD_OVGE_Update.sql (~1,230 lines)

Field Validation Rules

Business Rule: DOB Future Date Restriction

The Date of Build (DOB) cannot be set to a future date if the vessel's current status indicates it is already in service or has completed its lifecycle:

-- Enforced in ABSD_HIST_Update too (primary check location):
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

Business Rule: A03_PUBST Valid Values

Business Rule: Callsign Validation

Callsign ranges are validated against EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES (updated March 2026 — previously used local Callsign_range table).

• Standard countries: First 2 characters of callsign matched against the range start/end values.
• Special countries (3-char prefix): Countries SZI, FIJ, EGY, SUD use the first 3 characters of the callsign for the range check. These countries have overlapping 2-char ranges that require the additional character to disambiguate.

-- Simplified callsign validation logic:
IF (SELECT B04_CNTY FROM ABSD_HIFL WHERE LRNO = @LRNO AND SEQNO = '00')
    IN ('SZI','FIJ','EGY','SUD')
BEGIN
    -- Use first 3 chars of callsign
    IF NOT EXISTS (SELECT 1 FROM EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES
        WHERE LEFT(@callsign, 3) BETWEEN RANGE_START AND RANGE_END
          AND COUNTRY_CODE = @flag)
        RAISERROR('Callsign range invalid', 16, 1) ...
END
ELSE
BEGIN
    -- Use first 2 chars of callsign
    IF NOT EXISTS (SELECT 1 FROM EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES
        WHERE LEFT(@callsign, 2) BETWEEN RANGE_START AND RANGE_END
          AND COUNTRY_CODE = @flag)
        RAISERROR('Callsign range invalid', 16, 1) ...
END

Cascade on B01_CALLSIGN change: When the callsign is updated on SEQNO='00', the trigger also updates the SUPPLEMENTAL table:

UPDATE SUPPLEMENTAL_ABSD_HIFL
SET CALLSIGN = (SELECT B01_CALLSIGN FROM inserted)
WHERE LRNO = @LRNO AND SEQNO = '00'

Business Rule: Panama Official Number

B02_OFFNO (official number) cannot contain a hyphen ('-') character when the vessel's flag is Panama (B04_CNTY = 'PAN'):

IF (SELECT B04_CNTY FROM ABSD_HIFL WHERE LRNO = @LRNO AND SEQNO = '00') = 'PAN'
    AND CHARINDEX('-', @B02_OFFNO) > 0
BEGIN
    RAISERROR('B02_OFFNO cannot contain a hyphen for Panama-flagged vessels', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
END

Business Rule: SBLDR_CMP Builder Code

The builder/shipbuilder company code must match a record in ABSD_CBSB. The lookup combines four key columns:

IF NOT EXISTS (SELECT 1 FROM ABSD_CBSB
    WHERE KEYC + KEYG + KEYN + KEYX = @SBLDR_CMP)
BEGIN
    RAISERROR('SBLDR_CMP does not match a record in ABSD_CBSB', 16, 1) ...
END

Staging

-- ABSD_OVGE_UPDATES staging (no cursor needed — OVGE is one row per LRNO)
DELETE FROM ABSD_OVGE_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_OVGE_UPDATES SELECT * FROM ABSD_OVGE WHERE LRNO IN (SELECT LRNO FROM inserted)

ABSD_SHIP_SEARCH Updates (set-based)

-- Callsign, official number, fishing number — set-based when those fields change
IF UPDATE(B01_CALLSIGN)
    UPDATE ABSD_SHIP_SEARCH SET CALLSIGN = i.B01_CALLSIGN
    FROM ABSD_SHIP_SEARCH JOIN inserted i ON ABSD_SHIP_SEARCH.LRNO = i.LRNO

IF UPDATE(B02_OFFNO)
    UPDATE ABSD_SHIP_SEARCH SET OFFICIALNO = i.B02_OFFNO
    FROM ABSD_SHIP_SEARCH JOIN inserted i ON ABSD_SHIP_SEARCH.LRNO = i.LRNO

IF UPDATE(B10_FISHNO)
    UPDATE ABSD_SHIP_SEARCH SET FISHINGNO = i.B10_FISHNO
    FROM ABSD_SHIP_SEARCH JOIN inserted i ON ABSD_SHIP_SEARCH.LRNO = i.LRNO

-- LUPD (last update date) — when J06_LNCHDATE changes
IF UPDATE(J06_LNCHDATE)
    UPDATE ABSD_SHIP_SEARCH SET LUPD = GETDATE()
    FROM ABSD_SHIP_SEARCH JOIN inserted i ON ABSD_SHIP_SEARCH.LRNO = i.LRNO

Primary Annotations (tblChanges)

Secondary Annotation Groups (tblAnnotationLogGeneral)

Fire condition: FOR UPDATE, INSERT on ABSD_OVNA — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVNA.ABSD_OVNA_Update.sql (~791 lines)

Key table structure: ABSD_OVNA has composite key LRNO + SEQ_NO (char(2) — note: SEQ_NO not SEQNO). The primary name record is SEQ_NO='00'; all history is SEQ_NO='01', '02', etc. in descending date order.

Field Validation Rules

Business Rule: G01_EFD Cross-Check with ABSD_STSE

The name effective date (G01_EFD) for the current record (SEQ_NO='00') cannot be prior to the vessel's steel section completion date (C01_EFD from ABSD_STSE), unless there are historical status records indicating the vessel was laid down before the current record:

-- G01_EFD for SEQNO='00' must not be before ABSD_STSE.C01_EFD
-- Exception: allowed if ABSD_STSE.SEQNO > '01' exists for this vessel
IF (SELECT COUNT(*) FROM inserted WHERE SEQ_NO = '00') > 0
BEGIN
    IF (SELECT G01_EFD FROM inserted WHERE SEQ_NO='00') <
       (SELECT C01_EFD FROM ABSD_STSE WHERE LRNO = @LRNO AND SEQNO = '00')
       AND NOT EXISTS (SELECT 1 FROM ABSD_STSE WHERE LRNO = @LRNO AND SEQNO > '01')
    BEGIN
        RAISERROR('G01_EFD cannot be prior to C01_EFD in ABSD_STSE', 16, 1) ...
    END
END

Business Rule: G01_NAME Must Be All Upper Case (SEQNO='00')

The current vessel name must be stored in all upper case. The trigger uses a VARBINARY comparison (not UPPER() function) to detect any mixed-case characters — this is a deliberate design to prevent case-normalisation from hiding data quality issues:

-- Binary case check — not a collation comparison
IF CAST(G01_NAME AS VARBINARY) != CAST(UPPER(G01_NAME) AS VARBINARY)
BEGIN
    RAISERROR('G01_NAME must be all upper case for current record (SEQNO=00)', 16, 1) ...
END

Business Rule: G01_NAME No Trailing Spaces (SEQNO='00')

IF RIGHT(G01_NAME, 1) = ' '
BEGIN
    RAISERROR('G01_NAME cannot have trailing spaces', 16, 1) ...
END

Business Rule: G01_NAME Not Equal to Latest Ex-Name (SEQNO='00')

The current name cannot match the most recent historical name (the ex-name at minimum SEQ_NO ≠ '00'), to prevent accidental "rename to same name" operations:

IF EXISTS (SELECT 1 FROM ABSD_OVNA prev
    WHERE prev.LRNO = @LRNO
      AND prev.SEQ_NO = (SELECT MIN(SEQ_NO) FROM ABSD_OVNA WHERE LRNO = @LRNO AND SEQ_NO != '00')
      AND UPPER(prev.G01_NAME) = UPPER(@G01_NAME))
BEGIN
    RAISERROR('Current name cannot be same as the latest historical name', 16, 1) ...
END

Business Rule: Consecutive Name Duplicate Check

A name at SEQ_NO N cannot equal the name at SEQ_NO N±1 (consecutive names must differ):

-- Check the immediately adjacent sequence records for duplicate names
IF EXISTS (SELECT 1 FROM ABSD_OVNA adj
    WHERE adj.LRNO = @LRNO
      AND (adj.SEQ_NO = CAST(@SEQ_NO_INT - 1 AS CHAR(2)) OR adj.SEQ_NO = CAST(@SEQ_NO_INT + 1 AS CHAR(2)))
      AND UPPER(adj.G01_NAME) = UPPER(@G01_NAME))
BEGIN
    RAISERROR('Consecutive name records cannot have the same name', 16, 1) ...
END

Business Rule: G01_LNCHD_AS='Y' Uniqueness

Only one name record per LRNO may have G01_LNCHD_AS='Y' (the name used at vessel launch):

IF (SELECT COUNT(*) FROM ABSD_OVNA WHERE LRNO = @LRNO AND G01_LNCHD_AS = 'Y'
    AND SEQ_NO != @SEQ_NO) > 0
BEGIN
    RAISERROR('Only one LNCHD_AS=Y record is allowed per vessel', 16, 1) ...
END

Business Rule: G01_COMP_AS='Y' Uniqueness

Only one name record per LRNO may have G01_COMP_AS='Y' (the name at vessel completion).

Business Rule: G01_ORIG_NAME Must Have At Least One 'Y' or 'U' Per LRNO

Every vessel must have at least one name record with G01_ORIG_NAME='Y' or 'U'. If the current update would leave zero such records, it is rejected:

IF (SELECT COUNT(*) FROM ABSD_OVNA WHERE LRNO = @LRNO AND G01_ORIG_NAME IN ('Y','U')) = 0
BEGIN
    RAISERROR('At least one name record must have ORIG_NAME = Y or U', 16, 1) ...
END

J06 Audit (written to ABSD_OVGE)

ABSD_OVNA does not have its own J06 fields. The J06_AUTHOR and J06_LNCHDATE are written to the linked ABSD_OVGE record instead:

UPDATE ABSD_OVGE
SET J06_AUTHOR = UPPER(LEFT(RIGHT(system_user, LEN(system_user) - CHARINDEX('\',system_user)), 3)),
    J06_LNCHDATE = ...
WHERE ABSD_OVGE.LRNO IN (SELECT LRNO FROM inserted)

Staging

DELETE FROM ABSD_OVNA_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_OVNA_UPDATES SELECT * FROM ABSD_OVNA WHERE LRNO IN (SELECT LRNO FROM inserted)

AWSH Updates

-- AWSH: G01 column set to 'Y'
UPDATE ABSD_AWSH_UPDATES SET G01='Y' WHERE LRNO IN (SELECT LRNO FROM inserted)

ABSD_SHIP_SEARCH Update

-- VESSELNAME: only updated for SEQ_NO = '00' (current name)
IF (SELECT COUNT(*) FROM inserted WHERE SEQ_NO = '00') > 0
    UPDATE ABSD_SHIP_SEARCH SET VESSELNAME = (SELECT G01_NAME FROM inserted WHERE SEQ_NO = '00')
    WHERE LRNO = @LRNO

Primary Annotations (tblChanges)

Secondary Annotation Groups

Fire condition: FOR UPDATE, INSERT on ABSD_OVTY — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVTY.ABSD_OVTY_Update.sql (~324 lines)

Key structure: ABSD_OVTY has composite key LRNO + SEQ_NO. Type fields: D01_INT_BASIC (4 chars) + D01_TYP_A through D01_TYP_E (each 2 chars each, forming the full 20-char = 4+2+2+2+2+2+6 type code; the trailing 6 chars come from other fields).

Field Validation Rules

Business Rule: 20-Byte Ship Type Combo Validation

The complete 20-byte type combination (D01_INT_BASIC + TYP_A + TYP_B + TYP_C + TYP_D + TYP_E) must exist in tblMasterShipTypeXref with the correct STAT5CODE from SUPPLEMENTAL_ABSD_OVTY:

-- Exception: if the combined type = '19000990' (unknown/unclassified), skip validation
IF @fullType != '19000990'
BEGIN
    DECLARE @stat5 VARCHAR(10)
    SELECT @stat5 = STAT5CODE FROM SUPPLEMENTAL_ABSD_OVTY WHERE LRNO = @LRNO AND SEQ_NO = @SEQ_NO

    IF NOT EXISTS (
        SELECT 1 FROM tblMasterShipTypeXref
        WHERE TypeCode = @fullType
          AND STAT5CODE = @stat5)
    BEGIN
        RAISERROR('The ship type combination does not match tblMasterShipTypeXref for this STAT5CODE', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
END

Business Rule: Berthed / Decked Cross-Check

The BERTHED and DECKED flags constrain the value of D01_INT_BASIC at specific character positions:

IF @BERTHED = 1 AND @DECKED = 1
BEGIN
    IF SUBSTRING(@INITBASIC,3,1) != '1' OR SUBSTRING(@INITBASIC,5,1) != '4'
        RAISERROR('BERTHED+DECKED requires INITBASIC pos3=1 and pos5=4', 16, 1) ...
END
ELSE IF @BERTHED = 1
BEGIN
    IF SUBSTRING(@INITBASIC,3,1) != '1'
        RAISERROR('BERTHED requires INITBASIC pos3=1', 16, 1) ...
END
ELSE IF @DECKED = 1
BEGIN
    IF SUBSTRING(@INITBASIC,5,1) != '4'
        RAISERROR('DECKED requires INITBASIC pos5=4', 16, 1) ...
END

Business Rule: Star Rating Reset on Type Change

When the vessel type changes on the current record (SEQ_NO='00') and the vessel has a star rating above 1, the star rating is automatically reset to 1:

IF UPDATE(D01_INT_BASIC) AND @SEQ_NO = '00'
BEGIN
    IF (SELECT CHStars FROM tblStars WHERE LRNO = @LRNO) > 1
    BEGIN
        UPDATE tblStars
        SET CHStars = 1,
            CHStarsLUpd = GETDATE(),
            CHStarsUser = @Initials
        WHERE LRNO = @LRNO
    END
END

J06 Audit

Writes to ABSD_OVGE (same pattern as all vessel triggers).

Post-Update Cascade: ABSD_OVNA.SHIP_TYP

After the type is updated on SEQ_NO='00', the trigger updates the denormalized type string on the OVNA record:

-- Sync SHIP_TYP to ABSD_OVNA for current record
IF @SEQ_NO = '00'
    UPDATE ABSD_OVNA
    SET SHIP_TYP = @fullType  -- concatenated D01_INT_BASIC + TYP_A through TYP_E
    WHERE LRNO = @LRNO AND SEQ_NO = '00'

Staging

DELETE FROM ABSD_OVTY_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_OVTY_UPDATES SELECT * FROM ABSD_OVTY WHERE LRNO IN (SELECT LRNO FROM inserted)

ABSD_SHIP_SEARCH Update

-- SEQNO='00' only
-- VESSELTYPE = ABSD_CBSD.DECODE_A where INBAS_SUB = D01_INT_BASIC
-- TWENTYBYTESHIPTYPE = concatenated 20-byte type code
IF (SELECT COUNT(*) FROM inserted WHERE SEQ_NO = '00') > 0
BEGIN
    UPDATE ABSD_SHIP_SEARCH
    SET VESSELTYPE = (SELECT DECODE_A FROM ABSD_CBSD WHERE INBAS_SUB = @INT_BASIC),
        TWENTYBYTESHIPTYPE = @fullType
    WHERE LRNO = @LRNO
END

Primary Annotations (tblChanges)

Secondary Annotation Groups

Fire condition: FOR UPDATE, INSERT on ABSD_OVCA — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVCA.ABSD_OVCA_Update.sql (~123 lines)

Key structure: ABSD_OVCA has composite key LRNO + SEQ_NO. The current casualty notation is at SEQ_NO='01' (note: SEQ_NO='00' is not typically used; casualty notations start at '01'). Created/managed by spMakeNewCas which re-sequences DESC.

Field Validation Rules

Commented-Out Business Rule: F14_NOTES Not Null When Vessel Lost

The following cross-check was written but disabled in the source (SDG comment: "can't get it to work — on the to do list"):

/* REMMED OUT — NOT ACTIVE
When A02_STS IN ('T','B','D','W','H') AND A03_PUBST IN ('D','Y') AND F14_NOTES IS NULL:
    RAISERROR('When A03_PUBST is D or Y and A02_STS is T B D W or H
               then F14_NOTES cannot be null. Enter a casualty note', 16, 1)
*/

J06 Audit + Staging

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

DELETE FROM ABSD_OVCA_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_OVCA_UPDATES SELECT * FROM ABSD_OVCA WHERE LRNO IN (SELECT LRNO FROM inserted)

No Primary Annotations

ABSD_OVCA_Update does not write primary annotations to tblChanges.

Secondary Annotation Group

Fire condition: FOR UPDATE, INSERT on ABSD_OVCS — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVCS.ABSD_OVCS_Update.sql (~85 lines)

Field Validation Rules

Business Rule: Classification Society Code (CBUB1 Table 57)

The CODE must have a matching record in ABSD_CBUB1 where FLDI='57'. The comparison is case-sensitive (SQL_Latin1_General_CP1_CS_AS collation):

IF UPDATE(CODE)
BEGIN
    IF NOT (SELECT COUNT(*)
        FROM ABSD_OVCS
        INNER JOIN ABSD_CBUB1 ON ABSD_OVCS.CODE COLLATE SQL_Latin1_General_CP1_CS_AS = ABSD_CBUB1.CODE
            AND ABSD_CBUB1.FLDI = '57'
        WHERE LRNO IN (SELECT LRNO FROM INSERTED)) > 0
    BEGIN
        RAISERROR('The code in CODE does not match a record in table 57 of CBUB1', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
END

J06 Audit + Staging

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

DELETE FROM ABSD_OVCS_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted)
INSERT ABSD_OVCS_UPDATES SELECT * FROM ABSD_OVCS WHERE LRNO IN (SELECT LRNO FROM inserted)

Secondary Annotation Group

Fire condition: FOR UPDATE, INSERT on ABSD_OVDOC — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVDOC.ABSD_OVDOC_Update.sql (~169 lines)

Key structure: ABSD_OVDOC has composite key LRNO + SeqNo. Current DOC record is at SeqNo='00'. Contains: OWCODE (issuing company), EffectiveDate, Source, EDIT_DATE.

No Field Validation

ABSD_OVDOC_Update does not validate individual field values (no valXxx calls). All validation is handled at the application level before insert/update.

IsNew / IsCurrent / Notes Logic

J06 Audit (with J06_LNCHTIME)

ABSD_OVDOC additionally sets J06_LNCHTIME (HHMMSS) — one of the few triggers that does so:

IF UPDATE(OWCODE) OR UPDATE(SEQNO)
    UPDATE ABSD_OVGE
    SET J06_AUTHOR   = LEFT(@Initials, 3),
        J06_LNCHDATE = ...,
        J06_LNCHTIME = CAST(REPLICATE('0', 2-LEN(DATEPART(hour,   GETDATE()))) AS VARCHAR(2)) + CAST(DATEPART(hour,   GETDATE()) AS VARCHAR(2))
                     + CAST(REPLICATE('0', 2-LEN(DATEPART(minute, GETDATE()))) AS VARCHAR(2)) + CAST(DATEPART(minute, GETDATE()) AS VARCHAR(2))
                     + CAST(REPLICATE('0', 2-LEN(DATEPART(second, GETDATE()))) AS VARCHAR(2)) + CAST(DATEPART(second, GETDATE()) AS VARCHAR(2))
    FROM inserted WHERE ABSD_OVGE.LRNO = INSERTED.LRNO

Note: J06 only fires when OWCODE or SEQNO changes — not for any metadata field change.

ABSD_SHIP_SEARCH Update (cursor-based)

When OWCODE changes for SeqNo='00', the trigger updates the SHIP_SEARCH denormalized columns from the ABSD_OWGE company table:

-- SEQNO='00' only
IF UPDATE(OWCODE)
    UPDATE ABSD_SHIP_SEARCH
    SET DOCCODE    = INSERTED.OWCODE,
        DOCCOMPANY = ABSD_OWGE.SHNAME,    -- company short name
        DOCCOD     = ABSD_OWGE.NATY1      -- company nationality/flag
    FROM ABSD_SHIP_SEARCH
        INNER JOIN INSERTED ON ABSD_SHIP_SEARCH.LRNO = INSERTED.LRNO AND INSERTED.SEQNO = '00'
        INNER JOIN ABSD_OWGE ON INSERTED.OWCODE = ABSD_OWGE.OWCODE

Primary Annotations (tblChanges)

Secondary Annotation

Fire condition: FOR UPDATE, INSERT on ABSD_OVSMC — guarded by TRIGGER_DISABLE.
Source file: Triggers/dbo.ABSD_OVSMC.ABSD_OVSMC_Update.sql (~187 lines)

Key structure: ABSD_OVSMC has composite key LRNO + SEQNO. Current SMC record is at SEQNO='00'. Contains: OWCODE (issuing company), date_issued, date_expires, Source, EDIT_DATE, and SMC detail fields.

No Field Validation

ABSD_OVSMC_Update does not call any valXxx SPs. No field-level validation enforced in trigger.

IsNew / IsCurrent / Notes Logic

J06 Audit (with J06_LNCHTIME)

Like OVDOC, OVSMC also sets J06_LNCHTIME. Fires when OWCODE or date_issued changes:

IF UPDATE(OWCODE) OR UPDATE(date_issued)
    UPDATE ABSD_OVGE
    SET J06_AUTHOR   = LEFT(@Initials, 3),
        J06_LNCHDATE = ...,
        J06_LNCHTIME = ... (HHMMSS)
    FROM inserted WHERE ABSD_OVGE.LRNO = INSERTED.LRNO

No ABSD_SHIP_SEARCH Update

The SHIP_SEARCH update code was present in the original trigger but was explicitly removed (commented out with note "Removed to OVDOC 06/03/2007 REJ"). SMC issuer data is no longer maintained in SHIP_SEARCH — only the DOC issuer is (via ABSD_OVDOC_Update).

Primary Annotations (tblChanges)

Secondary Annotation Group