---
title: "SQL-004: Insert preference row"
summary: Inserts a new USER_PREFERENCE row for an application/page/user combination.
status: draft
owner: <owner>
last_reviewed: 2026-07-23
library: SAIF.Framework.Preferences
reference_type: package
---

# SQL-004: Insert preference row

This INSERT creates a new `USER_PREFERENCE` row, generating its primary key inline via `USER_PREFERENCE_SEQ.nextval` (see [SQL-001](SQL-001-insert-preference-sequence.md)). It backs `PreferenceData.InsertPref`, invoked from `SavePreferences` when `DoesPrefExist` returns `false`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-004 |
| **Operation type** | Create |
| **Execution method** | Inline SQL (`SqlHelper.ScalarQuery<int>.Execute`) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `PreferenceData` |
| **Criteria property** | n/a (no `IBusinessCriteria`) |
| **Lifecycle method** | `InsertPref` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:userid` | Input | string (`SqlHelper.NewParm`) | 40 | Lower-cased user ID for the new row. |
| `:appname` | Input | string (`SqlHelper.NewParm`) | 40 | Application name for the new row. |
| `:pagename` | Input | string (`SqlHelper.NewParm`) | 40 | Page name for the new row (always bound in INSERT, unlike the conditional binding in SELECT/UPDATE/DELETE/exists checks). |
| `:prefs` | Input | string (`SqlHelper.NewParm`) | 4000 | Trimmed (`preferenceText.Trim()`) JSON preference payload to store in `PREFERENCE_TEXT`. |
| `:updated` | Input | DateTime (`SqlHelper.NewParm`) | N/A | `System.DateTime.Now` — audit timestamp stored in `UPDATE_DATE`. |
| `:updateby` | Input | string (`SqlHelper.NewParm`) | 40 | `DPI.UserID` — the data-portal's authenticated user, stored in `UPDATE_USER` as the audit updater. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [USER_PREFERENCE](../database-tables.md#user_preference) | INSERT | `USER_PREFERENCE_SEQ_NUM`, `USER_ID`, `APP_NAME`, `PAGE_NAME`, `PREFERENCE_TEXT`, `UPDATE_DATE`, `UPDATE_USER` |

## Sample SQL

```sql
INSERT INTO USER_PREFERENCE (
    USER_PREFERENCE_SEQ_NUM,
    USER_ID,
    APP_NAME,
    PAGE_NAME,
    PREFERENCE_TEXT,
    UPDATE_DATE,
    UPDATE_USER
)
VALUES (
    USER_PREFERENCE_SEQ.nextval,
    :userid,
    :appname,
    :pagename,
    :prefs,
    :updated,
    :updateby
)
```

## Operations and processes
- [Insert preference row](../operations-processes.md#insert-preference-row)
- [Save (upsert/delete) preference](../operations-processes.md#save-upsertdelete-preference)

## Business rules
- [BR-001: User IDs are normalized to lower case](../business-rules.md#br-001-user-ids-are-normalized-to-lower-case)
- [BR-003: Preference identity is scoped by app, user, and optional page](../business-rules.md#br-003-preference-identity-is-scoped-by-app-user-and-optional-page)
- [BR-005: Preference text is trimmed before storage](../business-rules.md#br-005-preference-text-is-trimmed-before-storage)

## Sources
- SAIF.Framework.Preferences\PreferenceData.cs