Change Handlers Using SQL
You can leverage SQL commands to validate user input and save data changes to a database when cell values change.
Define these handlers in the xls.handlers table for all columns or just the specific ones you need.
Here's a sample of column-level handlers:
| ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
|---|---|---|---|---|---|---|---|---|
| s02 | view_cashbook3 | date | Change | s02 | usp_cashbook3_date_change | CODE | UPDATE s02.cashbook SET [date] = @cell_date_value WHERE id = @id | |
| s02 | view_cashbook3 | account_id | Change | s02 | usp_cashbook3_account_id_change | CODE | UPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id | |
| s02 | view_cashbook3 | item_id | Change | s02 | usp_cashbook3_item_id_change | CODE | UPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id | |
| s02 | view_cashbook3 | company_id | Change | s02 | usp_cashbook3_company_id_change | CODE | UPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id | |
| s02 | view_cashbook3 | debit | Change | s02 | usp_cashbook3_debit_change | CODE | UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id | |
| s02 | view_cashbook3 | credit | Change | s02 | usp_cashbook3_credit_change | CODE | UPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id |
These handlers also work with SQLite.
Keep in mind that SQL Server and SQLite use parameters prefixed with the @ character, while other database platforms use a colon, like :cell_value.