SQL-005: Update preference row

This UPDATE replaces PREFERENCE_TEXT and its audit columns for an existing row, guarded by a no-op check (PREFERENCE_TEXT <> :prefs). It backs PreferenceData.UpdatePref, invoked from SavePreferences when DoesPrefExist returns true.

Classification

Field	Value
SQL ID	SQL-005
Operation type	Update
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	UpdatePref

Parameters

Parameter	Direction	Type	Max length	Description
:prefs	Input	string (SqlHelper.NewParm)	4000	Trimmed preference text; bound once but referenced twice in the statement — in the SET PREFERENCE_TEXT = :prefs clause and again in the WHERE PREFERENCE_TEXT <> :prefs no-op guard.
:updated	Input	DateTime (SqlHelper.NewParm)	N/A	System.DateTime.Now — new audit timestamp for UPDATE_DATE.
:updateby	Input	string (SqlHelper.NewParm)	40	DPI.UserID — new audit updater for UPDATE_USER.
:userid	Input	string (SqlHelper.NewParm)	40	Lower-cased user ID; row-selection predicate.
:appname	Input	string (SqlHelper.NewParm)	40	Application name; row-selection predicate.
:pagename	Input	string (SqlHelper.NewParm)	40	Optional page name; only bound when pageName is non-empty.

Tables

Table	Access	Columns used
USER_PREFERENCE	UPDATE	PREFERENCE_TEXT, UPDATE_DATE, UPDATE_USER, USER_ID, APP_NAME, PAGE_NAME

Sample SQL

-- Default form (no page name supplied)
Update USER_PREFERENCE
set PREFERENCE_TEXT = :prefs,
    UPDATE_DATE     = :updated,
    UPDATE_USER     = :updateby
where USER_ID  = :userid
  and APP_NAME = :appname
  and PREFERENCE_TEXT <> :prefs
  and PAGE_NAME is NULL

-- When pageName is supplied, the final predicate is replaced with:
Update USER_PREFERENCE
set PREFERENCE_TEXT = :prefs,
    UPDATE_DATE     = :updated,
    UPDATE_USER     = :updateby
where USER_ID  = :userid
  and APP_NAME = :appname
  and PREFERENCE_TEXT <> :prefs
  and PAGE_NAME = :pagename

Operations and processes

• Update preference row
• Save (upsert/delete) preference

Business rules

• BR-001: User IDs are normalized to lower case
• BR-003: Preference identity is scoped by app, user, and optional page
• BR-004: Update is skipped when stored value is unchanged
• BR-005: Preference text is trimmed before storage

Sources

• SAIF.Framework.Preferences\PreferenceData.cs