SQL-026: CertificateHolder update (edit re-map)

The update-mode CertificateHolder constructor re-maps the edit contract onto the previously loaded holder and stamps UpdateUser/UpdateDate. NHibernate persists the change via dirty-check detection at flush*.

Classification

Field	Value
SQL ID	SQL-026
Operation type	Update*
Execution method	ORM (dirty-check flush*)
Database	Oracle*
Schema	[unqualified]**
Command type	N/A — ORM (NHibernate generates SQL at runtime from the ClassMaps in Data/SAIF.Data.NHibernate/Mappings/**)
Business object	CertificateHolder
Criteria property	N/A — ORM
Lifecycle method	CertificateHolder

Tables

Table	Access	Columns used
CERTIFICATE_HOLDER	UPDATE	POLICY_NUM, CERT_HOLD_NAME1, CERT_HOLD_NAME2, EXPIRED_FLAG, SUPERSEDED_FLAG, CERT_HOLD_ADDR1, CERT_HOLD_ADDR2, CERT_HOLD_ADDR3, CERT_HOLD_CITY, CERT_HOLD_STATE, CERT_HOLD_COUNTRY, CERT_HOLD_ZIP, CERT_HOLD_ZIP4, FOREIGN_ADDRESS_FLAG, CERT_HOLD_PHONE, CERT_HOLD_FAX, CERT_HOLD_EMAIL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, SUPERSEDED_BY_CERT_HOLD_SEQNUM, CERT_HOLD_SEQ_NUM

LINQ statement

Source: Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:49

// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:42-51
public CertificateHolder(IRepositoryFactory factory, string userId, long id, CertificateHolderEdit certificateHolderEdit)
{
    this.userId = userId;
    CertificateHolderQueryProvider queryProvider = new CertificateHolderQueryProvider(factory);
    DataItem = queryProvider.GetCertificateHolder(id);
    MapContractToDataObject(DataItem, certificateHolderEdit);

    DataItem.UpdateUser = userId;
    DataItem.UpdateDate = DateTime.Now;
}

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
UPDATE CERTIFICATE_HOLDER
SET
  POLICY_NUM = :PolicyNumber,
  CERT_HOLD_NAME1 = :Name1,
  CERT_HOLD_NAME2 = :Name2,
  EXPIRED_FLAG = :IsExpired,
  SUPERSEDED_FLAG = :IsSuperseded,
  CERT_HOLD_ADDR1 = :AddressLine1,
  CERT_HOLD_ADDR2 = :AddressLine2,
  CERT_HOLD_ADDR3 = :AddressLine3,
  CERT_HOLD_CITY = :City,
  CERT_HOLD_STATE = :State,
  CERT_HOLD_COUNTRY = :CountryCode,
  CERT_HOLD_ZIP = :ZipCode,
  CERT_HOLD_ZIP4 = :PlusFourCode,
  FOREIGN_ADDRESS_FLAG = :IsForeign,
  CERT_HOLD_PHONE = :PhoneNumber,
  CERT_HOLD_FAX = :FaxNumber,
  CERT_HOLD_EMAIL = :EmailAddress,
  CREATE_USER = :CreateUser,
  CREATE_DATE = :CreateDate,
  UPDATE_USER = :UpdateUser,
  UPDATE_DATE = :UpdateDate,
  SUPERSEDED_BY_CERT_HOLD_SEQNUM = :SupersededByCertificateHolder
WHERE CERT_HOLD_SEQ_NUM = :Id

Translation notes: - No explicit Update() call: under the stateful strategy RepositoryBase.Update is a no-op and the UPDATE is emitted by NHibernate dirty-checking at Commit() flush (Legends: SAIF.Framework.Data.NHibernate RepositoryBase / business-rules). The mapping declares no DynamicUpdate(), so NHibernate sends every updatable mapped column, not only the ones changed here. - No version column (CertificateHolderMap.cs:41 commented out) — WHERE CERT_HOLD_SEQ_NUM = :Id only, no optimistic-concurrency predicate. - :IsExpired, :IsSuperseded, :IsForeign bound as 'Y'/'N'. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function). - GetCertificateHolder(id) (line 46) is a separate read in saif-data.

Operations and processes

• OP-DOM-17 CertificateHolder create/update/Expire/Reinstate/Supersede — normalizes text/address and adds or updates the holder (see Operations and processes)

Business rules

• DOM-BR-170
• DOM-BR-171
• DOM-BR-172

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs