---
title: "SQL-009: CertificateHolder insert"
summary: Normalizes the holder's text/address fields and adds a new CertificateHolder entity.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-009: CertificateHolder insert

The create-mode `CertificateHolder` constructor normalizes the holder's text/address fields and adds a new `CertificateHolder` entity to the repository.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-009 |
| **Operation type** | Create |
| **Execution method** | ORM (IRepository.Add) |
| **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](../database-tables.md#certificate_holder) | INSERT | `CERT_HOLD_SEQ_NUM`, `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` |

## LINQ statement

Source: `Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:38`

```csharp
// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:30-38
DataItem = new certData.CertificateHolder();

MapContractToDataObject(DataItem, certificateHolderEdit);

DataItem.CreateUser = userId;
DataItem.CreateDate = DateTime.Now;
DataItem.UpdateUser = DataItem.CreateUser;
DataItem.UpdateDate = DataItem.CreateDate;
factory.GetRepository<certData.CertificateHolder>().Add(DataItem);
```

## 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.

```sql
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT CERT_HOLD_SEQUENCE.NEXTVAL
FROM DUAL

INSERT INTO CERTIFICATE_HOLDER (
  CERT_HOLD_SEQ_NUM,
  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)
VALUES (
  :Id,
  :PolicyNumber,
  :Name1,
  :Name2,
  :IsExpired,
  :IsSuperseded,
  :AddressLine1,
  :AddressLine2,
  :AddressLine3,
  :City,
  :State,
  :CountryCode,
  :ZipCode,
  :PlusFourCode,
  :IsForeign,
  :PhoneNumber,
  :FaxNumber,
  :EmailAddress,
  :CreateUser,
  :CreateDate,
  :UpdateUser,
  :UpdateDate,
  :SupersededByCertificateHolder)
```

Translation notes:
- `:Id` is the value fetched from `CERT_HOLD_SEQUENCE` (`GeneratedBy.Native("CERT_HOLD_SEQUENCE")` on Oracle).
- No version column is mapped (`Version` is commented out, `CertificateHolderMap.cs:41`).
- `:IsExpired`, `:IsSuperseded`, `:IsForeign` are bound as `'Y'`/`'N'` (`YesNoType`).
- Reference bind variables (`:<Property>`) carry the referenced entity's identifier value.
- The C# code assigns `DateTime.Now` client-side before flush; the value is bound as an ordinary parameter (no server date function).
- Values other than create/update audit fields come from `MapContractToDataObject` (line 32).
- Engine evidence: the Certs site registers its unit of work with the `"oracle"` factory (`DependencyMaps/SAIF.DependencyMaps.Web.Certs/DependencyMap.cs:95-96`).

## 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](../operations-processes.md))

## Business rules
- [DOM-BR-170](../business-rules.md#dom-br-170-holder-save)
- [DOM-BR-171](../business-rules.md#dom-br-171-holder-save)
- [DOM-BR-172](../business-rules.md#dom-br-172-holder-save)

> **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