Change Handlers Using SQL

Change Handlers Using SQL

You can use SQL commands to validate user input and save data changes to a database on cell changes.

You can define such handlers in the xls.handlers table for all columns or only the required columns.

Here is a sample of column-level handlers:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02view_cashbook3dateChanges02usp_cashbook3_date_changeCODEUPDATE s02.cashbook SET [date] = @cell_date_value WHERE id = @id
 s02view_cashbook3account_idChanges02usp_cashbook3_account_id_changeCODEUPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id
 s02view_cashbook3item_idChanges02usp_cashbook3_item_id_changeCODEUPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id
 s02view_cashbook3company_idChanges02usp_cashbook3_company_id_changeCODEUPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id
 s02view_cashbook3debitChanges02usp_cashbook3_debit_changeCODEUPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id
 s02view_cashbook3creditChanges02usp_cashbook3_credit_changeCODEUPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id

You can use such handlers even in SQLite and SQL Server Compact.

Note that SQL Server, SQL Server Compact, and SQLite use parameters with the @ character as a prefix while other database platforms use the colon like :cell_value.