10a — Triggers: Architecture & Universal Patterns

1. Architecture Overview

The ABSD_ schema uses SQL Server triggers as its sole mechanism for referential integrity, field validation, audit, and cross-table synchronization. There are no foreign-key constraints. Every constraint is enforced in T-SQL trigger code.

2. Guard Tables — When Triggers Fire

Every trigger body begins with a guard check. The trigger executes only when the guard table is empty.

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

3. Universal 10-Step Pattern

Nearly every ABSD_ UPDATE/INSERT trigger follows this structure:

4. J06 Audit Fields

All ship-related changes are time-stamped on ABSD_OVGE — the master vessel record — regardless of which table was actually modified. This gives a single "last touched" timestamp per vessel.

UPDATE ABSD_OVGE
SET
  J06_AUTHOR   = UPPER(LEFT(RIGHT(system_user, LEN(system_user) - CHARINDEX('\', system_user)), 3)),
  J06_LNCHDATE = RIGHT(YEAR(GETDATE()), 2)
               + REPLICATE('0', 2 - LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS VARCHAR(2))
               + REPLICATE('0', 2 - LEN(DAY(GETDATE())))   + CAST(DAY(GETDATE())   AS VARCHAR(2))
WHERE ABSD_OVGE.LRNO IN (SELECT LRNO FROM inserted)

5. SEQNO History Model

Most ABSD_ tables storing historical snapshots use a SEQNO CHAR(2) key alongside LRNO:

When a record is made historical:

1. Existing history is re-sequenced upward: '01'→'02', '02'→'03', etc.
2. The current '00' record is duplicated to '01'.
3. The '00' record is updated with the new values.

6. Annotation System

-- Primary annotation example
exec spGetEnglishFieldName 'absd_hist', 'A02_STS', @EnglishField output
INSERT INTO tblChanges (LRNo, CompanyNo, EffDate, ChangeType, UserInitials, Source, SourceRef,
    FilingRef, Confidential, FollowUpDate, FollowUpAssignedTo, FollowUpDone,
    OldValue, NewValue, Notes, ExtraData)
select i.LRNo, null, Cast(getdate() as varchar), @Englishfield, @Initials, @Source,
    @SourceRef, @FilingRef, 0, null, null, null,
    OLDVALUE = case @IsNew
        when 1 then case @IsCurrent when 1 then null else i.A02_STS end
        else d.A02_STS end,
    i.A02_STS, @Notes, null
from inserted i left join deleted d on i.lrno = d.lrno and i.seqno = d.seqno
    where i.LRNO = @CURR_RECORD AND i.SEQNO = @CURR_SEQ

-- Secondary annotation example
select @Grouping = Grouping, @ShipWAtchCol = shipWatchCOl from indAnnotation_SecondaryGroupings
where Tablename = 'ABSD_HIST' and Fieldname = 'A02_EFD'

insert into tblAnnotationLogGeneral (Grouping, ShipwatchCol, UserInitials, LRNo, COmpanyNo)
values (@Grouping, @ShipwatchCol, @Initials, @CURR_RECORD, null)

7. IsNew / IsCurrent / Notes Logic

Every trigger that writes primary annotations determines the change context from the pseudo-tables:

-- IsNew detection
select @IDno = LRNO from deleted where LRNO = @CURR_RECORD
if isnull(@idno,'') = ''
    set @IsNew = 1
else
    set @IsNew = 0

-- IsCurrent detection
select @CurrentSeq = @CURR_SEQ
if @CurrentSeq = '00'
    set @IsCurrent = 1
else
    set @IsCurrent = 0

OldValue logic in primary annotations:

OLDVALUE = case @IsNew
    when 1 then
        case @IsCurrent
            when 1 then null         -- new current: no prior value
            else i.<field>           -- new historical: treat new value as the "old" (it IS the old value)
        end
    else d.<field>                   -- update: get old value from deleted pseudo-table
end

8. Staging Tables (*_UPDATES)

Every ABSD_ table with a trigger has a corresponding *_UPDATES staging table (e.g., ABSD_OVGE_UPDATES, ABSD_OVNA_UPDATES). The trigger maintains these tables to mirror the current committed state of the row.

-- Standard staging pattern
IF EXISTS (SELECT * FROM ABSD_OVGE_UPDATES WHERE LRNO IN (SELECT LRNO FROM inserted))
    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)

Purpose: Downstream systems (interfaces, replication consumers) poll the *_UPDATES tables rather than the main tables. This decouples consumption from the source table and provides a stable snapshot of recently modified rows.

9. ABSD_SHIP_SEARCH Maintenance

ABSD_SHIP_SEARCH is a denormalized search index updated by triggers across many tables. It stores a single row per vessel containing key searchable values extracted from disparate tables.

LOA priority waterfall (ABSD_HILE → ABSD_SHIP_SEARCH.LOA):

LOA = CASE WHEN C02_LOA > 0 THEN C02_LOA
           WHEN C02_LBP > 0 THEN C02_LBP
           ELSE C02_REG
      END

10. Validation SP Conventions

All field validation is delegated to stored procedures. The return convention is consistent across all val* SPs:

SELECT @RETMESSAGE = 'fieldname ' + @RETMESSAGE  -- prefix with field name
RAISERROR (@RETMESSAGE, 16, 1)
ROLLBACK TRANSACTION
RETURN

11. Trigger File Index

Core Vessel Tables

Status & Flag History Tables

Ownership History Tables

Machinery Tables

FU* Cargo & Capacity Tables

Structure Tables

Casualty Tables

Company / Owner Tables

Codebook Tables

SUPPLEMENTAL_ABSD_* Tables

Misc & Annotation Tables

12. Documentation Files