10l – Miscellaneous Triggers

This page documents miscellaneous triggers that do not fit neatly into vessel, ownership, cargo, structural, casualty, codebook, or supplemental categories. It covers:

• 1ABSD_NCON — New-construction order register. The most complex trigger in the system: multi-table cascade, OWNC rebuild, EDM country validation (March 2026), and 6 satellite cascades.
• 2ABSD_NCON_CONFI — Confidential attributes for new-construction orders (shiptype, dimensions, FLAG). Additional FK checks and dimensional cross-checks.
• 3ABSD_SALE — Sale and scrap transaction records. Writes tblFSWSalesPage, enriched tblChanges audit via fnSalesDetail(), and J06 audit on effective-date change.
• 4tblChanges — Self-trigger on the primary audit log table. Automatically zero-pads CompanyNo to 7 digits on every insert.
• 5tblAnnotationLogGeneral — Self-trigger on the annotation log. Automatically populates Source field from the current user's settings on every insert.
• 6QH_ShipsOwner_Data — Questionnaire response system. One business trigger (updates QH_Questionnaires.LastResponseDate) plus three SQL Server merge-replication infrastructure triggers.
• 7ofac_sdn2 — OFAC Specially Designated Nationals list. Inserts and deletes write a tblChanges audit record (ChangeType='OFAC').
• 8PANDI — P&I Club table. No trigger files are present in the Triggers directory; enforcement for this domain is handled elsewhere.

The most complex trigger in the system. Validates new-construction order fields, enforces date rules, cascades to six satellite tables, rebuilds the entire ABSD_OWNC set from scratch, updates SHIP_SEARCH, and writes annotation and staging.

Guard & Cursor

• 1Standard TRIGGER_DISABLE guard at the top. Cursor iterates over inserted by LRNO.

Country Code Validation — EDM March 2026

• 2COB must exist in EDM.dbo.T_REF_COUNTRY (collate Latin1_General_CI_AI).
• 3PCOB (Previous Country of Build) must also exist in EDM.dbo.T_REF_COUNTRY when populated.

Status / Indicator Field Validation

• 4PIND (Program Indicator) must be one of: N, P, Y, C.
• 5RIND (Request Indicator) must be Y or NULL.
• 6SPSIND (Special Survey Indicator) must be Y or NULL.
• 7COMI (Commercially Ordered flag) validated as Y/N via valStandardYN.
• 8RET1 (Retention flag) validated as Y/N via valStandardYN.

Date Validation

• 9DATE_NCON (Order Date): validated via valStandard4_new. Additionally, must not be in the future — enforced by comparing fnrealdatetolrdate(getdate()) against the field value. This is one of the few "future date" blocks in the system.
• 10CDTE (Keel-laying date), PDTE (Planned delivery), KEST (Estimated keel), LEST (Estimated launch) — all validated via valStandard4_new.

Cascade to Satellite Tables

OWNC Rebuild — Critical Business Rule

SHIP_SEARCH Update

• 11Writes DATE_NCON to ABSD_SHIP_SEARCH to keep the search index current.

Annotation & Staging

• 12Writes annotation record for field C11I (order-date field) grouping.
• 13Inserts into ABSD_NCON_UPDATES staging table for downstream ETL processing.

Validates confidential new-construction attributes including ship type, hull shape, classification, full vessel dimensions, flag country, and contact details. Writes a tblChanges audit record for Shipname and Shipmanager changes.

Country / Flag Validation — EDM March 2026

Codebook FK Checks

• 1STAT5CODE must exist in tblMasterShipTypeXref.
• 2HULL_SHAPE must exist in ABSD_CBUB1 where FLDI='11' (hull shape codebook).
• 3CLASS must exist in ABSD_CBUB1 where FLDI='62' (classification society codebook).
• 4SHIPTYPE must exist in tblMasterShipTypeXref.inbassub.

Dimension Validation

• 5GROSS and DWT — maximum 7 digits.
• 6LBP ≤ LOA — Length Between Perpendiculars must not exceed Length Overall.
• 7LBP > Breadth — LBP must be greater than beam (exempted for shiptype starting with '7').
• 8LOA > Breadth — LOA must be greater than beam.
• 9LOA > Maxdraught — LOA must exceed maximum draught.
• 10LBP > Maxdraught — LBP must exceed maximum draught.

Date Validation

• 11DATE_NCON, CDTE, KEST, LEST — all validated via valStandard4.

tblChanges Audit

• 12Writes to tblChanges when Shipname changes — old and new values recorded.
• 13Writes to tblChanges when Shipmanager changes — old and new values recorded.
• 14Uses standard historical awareness: when the record is not current (SEQ ≠ '00'), the ChangeType is prefixed to indicate a historical amendment.

Manages the full lifecycle of sale and scrap transaction records. Writes to the vessel sales page (tblFSWSalesPage) when the effective date changes, produces enriched audit entries in tblChanges via fnSalesDetail(), and applies historical-awareness logic based on sequence number.

Guard & Cursor

• 1Standard TRIGGER_DISABLE guard. Cursor iterates over inserted by LRNO + SEQ_NO.

Sales Page Integration (SEQ='00' only)

• 2When SALE_EFD (Sale Effective Date) changes AND the record is current (SEQ_NO = '00'), updates tblFSWSalesPage to set the sale status to 'S' (Sold).
• 3Historical records (SEQ_NO ≠ '00') do not trigger a tblFSWSalesPage update — only the current record drives the sales page status.

tblChanges Audit

• 4Writes to tblChanges when Reported_Sold_To is not empty, OR when Sale_Price_USD > 0.
• 5The Notes field in tblChanges is populated using the scalar function dbo.fnSalesDetail(), which formats a rich human-readable summary of the sale (price, currency, buyer, date, etc.).
• 6Historical-awareness: When SEQ_NO ≠ '00', the ChangeType is prefixed with "Made historical" or "Historical amendment" to distinguish historical-record edits from current-record edits.

Annotation

• 7Writes an annotation record for field group Sale_Efd (sale effective date grouping).
• 8J06 audit is written when SALE_EFD changes.

Records the deletion of a sale record as an "Erroneous sales report" in tblChanges. Only fires for current-record deletions (SEQ='00').

Business Rules

• 1Standard TRIGGER_DISABLE guard. Cursor iterates over deleted.
• 2Only processes records where SEQ = '00' — deletion of historical records does not generate a tblChanges entry.
• 3Writes to tblChanges:
  • OldValue = 'Deleted'
  • NewValue = 'Deleted'
  • Notes = 'Erroneous sales report -' + dbo.fnSalesDetail()
• 4Writes J06 audit record.

Self-trigger on the tblChanges audit table. Automatically normalises CompanyNo to 7-digit zero-padded format on every insert, so that audit consumers always see a uniform company number format regardless of how the calling trigger formatted it.

Business Rules

• 1Standard TRIGGER_DISABLE guard. Cursor iterates over inserted rows.
• 2CompanyNo normalisation: If LEN(CompanyNo) < 7, pad with leading zeros to exactly 7 digits. Example: '1234' → '0001234'.
• 3If CompanyNo is already 7 or more digits, no change is made.
• 4This runs on every insert into tblChanges — including inserts made by OW* company triggers, SALE triggers, OFAC triggers, and any other system that writes to tblChanges.

Self-trigger on the tblAnnotationLogGeneral annotation table. Automatically populates the Source field on every new annotation entry by looking up the current user's source setting from tblUserSettings. This means callers never need to supply Source — it is always resolved at insert time from the user context.

Business Rules

• 1No TRIGGER_DISABLE guard. The trigger fires unconditionally on every insert into tblAnnotationLogGeneral.
• 2On each new annotation row: looks up tblUserSettings.currentsource WHERE username = SYSTEM_USER.
• 3Sets the Source field of the newly inserted annotation row to the retrieved currentsource value.
• 4If SYSTEM_USER has no row in tblUserSettings, Source remains as supplied by the caller (or NULL).

Business trigger for the ship-owner questionnaire system. When a questionnaire response record is actioned, updates the parent questionnaire's LastResponseDate to the current datetime.

Business Rules

• 1Fires on INSERT, UPDATE, and DELETE — but reads the ActionType from the Inserted table.
• 2Only executes the business logic when ActionType = 'R' (Response). Other action types are silently ignored.
• 3When ActionType = 'R': updates QH_Questionnaires.LastResponseDate = GetDate() for the matching QH_Questionnaires.ID = Inserted.QuestID.
• 4No TRIGGER_DISABLE guard — fires regardless of bulk-load mode.

SQL Server merge-replication change-tracking triggers (system-generated). These maintain ctsv_/tsvw_ tracking tables and must not be modified manually.

• 1All three triggers exit immediately if sessionproperty('replication_agent') = 1 AND trigger_nestlevel() = 1 — prevents replication loops.
• 2del_*: On delete, records rowguid + type=1 ('delete') into tsvw_A3FCC... with UPDATELINEAGE tracking. Removes the row from ctsv_A3FCC....
• 3ins_*: On insert, tracks the new row in ctsv_A3FCC... with lineage and column-vector (colv1). Handles 41 columns (@ccols = 41). Special logic for re-insert of a previously deleted rowguid (increments version counter).
• 4upd_*: On update, updates lineage and colv1 bit-map in ctsv_A3FCC... via UPDATECOLVBM. Prevents rowguid column updates (RAISERROR 20062 and rollback).
• 5All three use tablenick = 8648000 to identify the article in sysmergearticles.
• 6All use ANSI_NULLS OFF (note the SET at the top) — this is the standard SQL Server replication trigger pattern.

Records the addition of a vessel to the OFAC SDN sanctions list in tblChanges. No business validation — pure audit trail.

• 1No TRIGGER_DISABLE guard — fires unconditionally on every insert.
• 2Inserts one row per inserted row into tblChanges:
  • LRNo = i.IMO (maps OFAC IMO number to vessel LRNO)
  • CompanyNo = NULL
  • EffDate = CAST(getdate() AS varchar)
  • ChangeType = 'OFAC'
  • Source = 'OFAC'
  • OldValue = NULL
  • NewValue = i.identifier (the SDN designation added)
  • Confidential = 0

Records the removal of a vessel from the OFAC SDN sanctions list in tblChanges. No business validation — pure audit trail.

• 1No TRIGGER_DISABLE guard — fires unconditionally on every delete.
• 2Inserts one row per deleted row into tblChanges:
  • LRNo = d.IMO
  • CompanyNo = NULL
  • EffDate = CAST(getdate() AS varchar)
  • ChangeType = 'OFAC'
  • Source = 'OFAC'
  • OldValue = d.identifier (the SDN designation removed)
  • NewValue = NULL
  • Confidential = 0