10j — Codebook Triggers (CB* / tblFlexible* / tblStatCode5)

APSProperTRUD Formatting

• When TRUD (trade/use decode) is inserted, immediately computes and stores APSProperTRUD = dbo.APSProper(TRUD) back on the same row
• dbo.APSProper() is a scalar function that applies title-case formatting (capitalises first letter of each word)
• This denormalised proper-case field is used by APS (publication system) to avoid runtime formatting

WMKR='W' (Withdrawn) Protection Rules

• When a code is being marked as withdrawn (WMKR='W'), the trigger checks if the code is still in use on vessel records
• FLDI='01' (Stowage): Counts ABSD_FUCA2.D08_STOWAGE references. If any exist, raises: "Stowage code still exists on vessels, please correct" and rolls back
• FLDI='02' (Commodity): Counts ABSD_FUCA2.D08_COMMODITY references. If any exist, raises: "Commodity code still exists on vessels, please correct" and rolls back
• This prevents withdrawing a codebook entry that would leave vessel records with a dangling reference

APSProperTRUD Formatting (on UPDATE)

• On any update where TRUD changes, recomputes APSProperTRUD = dbo.APSProper(TRUD)
• Runs outside the cursor as a set-based UPDATE using INNER JOIN to inserted

SEQ_NO='00' → BLDR_XREF Update (Type E/S only)

• When OUT_NOTE_SEQ_NO='00' and OUT_CONO_KEY starts with 'E' or 'S' (fleet/shipbuilder type), the trigger dissects the combined 70-character note line into 6-character shipbuilder cross-reference codes
• Each 6-char slot contains a 3-char country code (XREF_CNTRY) + 3-char builder code (XREF_CDE1)
• Up to 10 cross-references are encoded in the 70-char string at positions 1, 8, 15, 22, 29, 36, 43, 50, 57, 64
• Deletes all existing BLDR_XREF rows for this CNTRY+CDE1 and re-inserts from the parsed note
• Only non-blank 6-char slots are inserted (blank slot = no cross-reference at that position)

Type 'S' + SEQ_NO > '19' → BLDR_TEXT TYPE='H' (History)

• For shipbuilder notes (OUT_TYPE='S') with sequence numbers above '19', the note text is written to BLDR_TEXT as history lines (TYPE='H')
• The SEQNO in BLDR_TEXT is calculated as: OUT_NOTE_SEQ_NO - 19 (zero-padded to 2 digits)
• First deletes the existing BLDR_TEXT TYPE='H' row for this SEQNO, then re-inserts with the current note line
• Uses OUT_CODE_REST from ABSD_CBCOROOT to derive CDE2 and CD key components

OUT_TYPE='F' (Fleet/Flag) → ABSD_CBEB

• Assembles a GENSTYLE display string (25 chars) from OUT_SHORT_NAME and status prefix
• Status prefix: 'T' = one leading space; 'N' = three spaces; 'W' = four spaces; default = no prefix
• APPEND_GENSTYLE suffix logic (mutually exclusive priority order):
  1. If OUT_TO has value → append -YY (last 2 digits of TO year)
  2. Else if OUT_FROM → append YY- (FROM year)
  3. Else if XREF_FLAG differs → append XREF_FLAG
  4. Else if XREF_BLDR differs → append XREF_BLDR
  5. Else if XREF_BLDR = BLDR and XREF_FLAG = FLAG → append #XREF_YARD
• Deletes existing ABSD_CBEB row for this MM key, then inserts new row with DECODE_1, DECODE_2, GENSTYLE, HIST, TOWN, DELT

OUT_TYPE='T' (Tender/Sub-builder Type) → ABSD_CBSB + BLDR_TEXT A/P

• Builds 22-char DECODE string using same status-prefix pattern as Type F
• Splits DECODE into DECODEA (2 chars) + DECODEB (20 chars)
• Fetches style from ABSD_CBCONAME (OUTNAME1+OUTNAME2) for this key
• Deletes and re-inserts ABSD_CBSB row with KEYC/KEYG/KEYN/KEYX/STYLE/DECODEA/DECODEB/HIST/TOWN/DELT
• Also rebuilds BLDR_TEXT TYPE='A' (address, up to 6 lines) from ABSD_CBCOADDR SEQNO='00'
• Rebuilds BLDR_TEXT TYPE='P' (phone/contact) from ABSD_CBCOCOMM: SEQNO 01=Phone, 02=Fax, 03=Telex, 04=Email, 05=Web — prefixed with label ("Phone: ", etc.)

OUT_TYPE='S' (Shipbuilder/Yard) → BLDR_NAME + BLDR_TEXT H/N/A/P + BLDR_XREF + SUPPLEMENTAL

• Status 'W' (withdrawn): Skips BLDR_NAME insert and BLDR_TEXT N/A/P blocks — withdrawn yards are not published
• BLDR_NAME: Inserts/replaces row with CNTRY/CDE1/CDE2/CD/NAME_ST/PORT/BLD_NAME/BLD_LOC
  • NAME_ST derived from status: 'C'=closed; else if OUT_TYPE_IND in (H,X,R) → use OUT_TYPE_IND; else use OUT_STATUS
  • PORT = town code from ABSD_CBCOADDR (must be 4 chars, else NULL)
  • BLD_LOC = concatenation of CBCONOTE lines with SEQ_NO between '10' and '19' (location notes)
• BLDR_TEXT TYPE='H' (history): Rebuilt from CBCONOTE SEQ_NO '20'–'49' — each line becomes a history text row in BLDR_TEXT with sequential SEQNO
• BLDR_TEXT TYPE='N' (notes): Rebuilt from CBCONOTE SEQ_NO='01', up to 4 lines
• BLDR_TEXT TYPE='A' (address) and TYPE='P' (phone/contact): Same logic as Type T above
• BLDR_XREF: Rebuilt from CBCONOTE SEQ_NO='00' — parses 70-char string into up to 10 × 6-char cross-reference codes
• SUPPLEMENTAL_ABSD_CBCOROOT: On UPDATE(OUT_SHORT_NAME), updates SHORT_NAME_PCASE = dbo.proper(out_short_name) for the matching key

Town Name Word-Wrap Algorithm (SEQ_NO='00' only)

The full port name is split into two 19-character halves for the mainframe-era TNS1/TNS2 fields in CBTO1. The split algorithm:

• Combines OUT_PUBD + OUT_NAME_FILLER into a single string; collapses all double-spaces into single spaces
• Applies type-based leading prefix from ABSD_CBPPROOT.OUT_PPRO_TYPE: P=no prefix, T=1 space, O=2 spaces, A=6 spaces, other=9 spaces
• Scans backward from position 19 to find the last space character — that position becomes the split point (RETPOS)
• If no space found before position 19, or if all characters before RETPOS are spaces, forces split at position 19
• TNS1 = SUBSTRING(name, 1, RETPOS); TNS2 = SUBSTRING(name, RETPOS+1, 19)
• Writes to ABSD_CBTO1 (INSERT if not exists, else UPDATE) for this CNTRY+TOWN

SEQ_NO='90' → Owner Stats (OWST1/OWST2)

• When OUT_PPNA_SEQ='90', the PUBD field holds the owner statistics line for this port
• Splits into OWST1 (first 35 chars) and OWST2 (chars 36–38 of PUBD + first 32 of FILLER)
• For all other SEQ_NO values, looks up the SEQ_NO='90' record to populate OWST1/OWST2 on the CBTO1 row
• If no SEQ_NO='90' exists, OWST1/OWST2 are written as blank padding

ABSD_CBTD Search/Display Index (SEQ_NO='00' only)

• SRCH field: strips all non-alphanumeric characters from the display name, uppercases, truncates to 25 chars
• DISP field: the display form of the name, truncated to 60 chars
• If display name is empty, deletes the CBTD row (port has no name → not searchable)
• INSERT if key does not exist in CBTD, otherwise UPDATE

Key Length Validation

• OUT_PPRO_KEY must be exactly 7 characters; shorter keys raise: "Key must be 7 characters" and roll back

OUT_PORT_SEQ Uniqueness

• When OUT_PORT_SEQ changes, validates that SUBSTRING(OUT_PPRO_KEY,1,3) + OUT_PORT_SEQ (country + port sequence) is unique across all CBPPROOT records
• Duplicate detected → "Country and Portsequence must be unique", rollback
• Note: A previous OUT_CNTY_SEQ check is commented out (MW 09/01/09 codebook modification)

OUT_LLP_PORT_CODE (Grid Reference) Uniqueness

• When OUT_LLP_PORT_CODE (Lloyd's List Port grid reference) is supplied and changes, validates it is globally unique across all CBPPROOT records
• Duplicate detected → "Country and Portsequence must be unique" (reuses same message), rollback

ABSD_CBTO1 Update — TCNT (LR Office Code)

• Sets ABSD_CBTO1.TCNT to OUT_LR_OFFICE for this CNTRY+TOWN
• If OUT_LR_OFFICE is blank (' '), falls back to OUT_INDICATOR
• INSERT if CBTO1 row does not yet exist, otherwise UPDATE

ABSD_CBPD Update — Full Port Details

• Maintains the complete port detail record with all geographic, navigational, and publication fields:
• Coordinates: LATDEG/LATMIN/LATSEC/NSIND + LNGDEG/LNGMIN/LNGSEC/EWIND
• Grid references: GRIDREF (LLP port code), MAP (map page), MARSDEN (Marsden grid square), SISZONE (SIS navigational zone)
• LLP Area/Port: LLPAREA1/LLPAREA2 + LLPPORT1/LLPPORT2
• Dock pages: WDPAGE (wet dock) + DDPAGE (dry dock)
• Functional codes: FUNC (port functions), PLACE (place marker)
• VER (coordinate version flag): 'C' if LATDEG between 0 and 360, else ' '
• DATEVAL: Derived from OUT_DATE_FROM/OUT_DATE_TO; if coordinates present and port not published (PUB not in Y/X), sets TO portion to '111'
• INSERT if CBPD row does not exist, otherwise full UPDATE of all fields

ABSD_CBTO1 Update — Town Name (TNS1/TNS2)

• After updating TCNT, also refreshes TNS1/TNS2 by looking up the CBPPNAME SEQ_NO='00' record for this key and re-applying the word-wrap algorithm (same logic as CBPPNAME trigger)
• This ensures CBTO1 town name stays consistent when the root record changes type or other attributes

OUT_STRE_DELETE Flag

• When OUT_STRE_DELETE='1', the trigger deletes the corresponding row from ABSD_CBTO2 (rather than inserting/updating)
• This is a soft-delete pattern: the CBPPSTRE record is kept for history, but the derived CBTO2 row is removed

ABSD_CBTO2 Maintenance (when not deleted)

• Writes STYL1, STYL2, STYL3 (three style/name fields for the street/district) to ABSD_CBTO2
• INSERT if CNTRY+TOWN+KEY does not exist, otherwise UPDATE all three style fields

LEVEL5DECODE Cascade to tblMasterShipTypeXref

• Fires only when LEVEL5DECODE changes (IF UPDATE(LEVEL5DECODE))
• Outer cursor iterates over all changed Level5 codes in the inserted set
• Inner cursor finds all rows in tblMasterShipTypeXref where Stat5Code = @Level5
• Updates tblMasterShipTypeXref.Stat5Decode = @Level5Decode for each matching row
• This keeps the master ship-type cross-reference table in sync when a Level 5 decode description changes

Uniqueness Enforcement

• Looks up tblFlexible_Bucket_CB.UNIQUE for the inserted FIELD_NAME
• If UNIQUE='Y', counts existing rows with the same KEY + FIELD_NAME combination
• If count exceeds 1 (i.e., a duplicate would exist after this insert/update), raises: "Data Already Exists for this Field, Field Must be Unique" and rolls back

Dual-Context tblChanges Audit

• FIELD_TYPE='S' (Ship/Vessel field): Uses spGetUserSettings for user context; writes to tblChanges with LRNo = [KEY], ChangeType='Data Bucket', and Notes = FIELD_NAME
• FIELD_TYPE='C' (Company/Owner field): Uses spGetUserSettingsOwners for user context; writes to tblChanges with CompanyNo = [KEY], ChangeType='Data Bucket', and Notes = FIELD_NAME
• The FIELD_TYPE is resolved by joining to tblFlexible_Bucket_CB at audit time

FIELD_NAME Special Character Validation

• When FIELD_NAME is inserted or updated, validates it via dbo.fnCheckFlexDataBucketFieldName(@FIELD_NAME)
• The scalar function returns 0 if the field name contains special characters
• On failure, raises: "Special Characters Cannot be Used in a Field Name" and rolls back
• This prevents field names that would break dynamic SQL or serialisation in downstream consumers of the bucket