---
title: "SQL-027: CertificateHolder expire update"
summary: Sets IsExpired to true and stamps the updating user on the loaded holder.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-027: CertificateHolder expire update

`CertificateHolder.Expire` sets `IsExpired` to true and stamps the updating user on the loaded `CertificateHolder`. NHibernate persists the change via dirty-check detection at flush*. (`Reinstate` performs the inverse, setting `IsExpired` to false.)

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-027 |
| **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** | `Expire` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CERTIFICATE_HOLDER](../database-tables.md#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:55`

```csharp
// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:53-58
public void Expire()
{
    DataItem.IsExpired = true;
    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.

```sql
-- 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 — no optimistic-concurrency predicate.
- `:IsExpired` = `'Y'` (`YesNoType`, `CertificateHolderMap.cs:19`). `Reinstate()` (lines 60-65) produces the same shape with `'N'`.
- The C# code assigns `DateTime.Now` client-side before flush; the value is bound as an ordinary parameter (no server date function).
- Prior page named the column `IS_EXPIRED`; the mapped column is `EXPIRED_FLAG`.

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