Cell Change Handlers
DBEdit allows executing server-side stored procedures or SQL codes to handle cell change events.
DBEdit highlights the yellow arrow button if the active table has such handlers:
The simplest way to attach the handler is to create a procedure with the same name and the _change suffix.
For example, the s02.usp_cashbook3_change procedure handles change events of the s02.usp_cashbook3 procedure.
You can use a handler configuration table like xls.handlers or a view to link handlers.
For example:
In this sample, you can see that view_cashbook2 has itself as a change handler. So, DBEdit just commits every change immediately.
The view_cashbook3 view has multiple column-level handlers defined inline.
For example, the debit column handler has the following SQL code in the HANDLER_CODE field:
UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id
It just updates the debit column with a new value.
The usp_cashbook3 procedure has the usp_cashbook3_change handler (this row is optional as the handler has the _change suffix).
The usp_cashbook3_change procedure has the following code:
CREATE PROCEDURE s01.usp_cashbook3_change @column_name nvarchar(255) , @cell_value nvarchar(255) = NULL , @cell_number_value money = NULL , @cell_datetime_value date = NULL , @id int = NULL AS BEGIN SET NOCOUNT ON IF @column_name = 'debit' UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id ELSE IF @column_name = 'credit' UPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id ELSE IF @column_name = 'item_id' UPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id ELSE IF @column_name = 'company_id' UPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id ELSE IF @column_name = 'account_id' UPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id ELSE IF @column_name = 'date' UPDATE s02.cashbook SET [date] = @cell_datetime_value WHERE id = @id END
The procedure has the @id parameter that receives the value of the id column.
If the column contains characters that not supported in parameter names, DBEdit escapes the names.
Also, the procedure uses special parameters populated by DBEdit with context values like the column name, cell value, cell number value, and the cell datetime value.
Below is a complete list of such context parameters.
Note that you can use cell change handlers to check the user's input and notify the user or rollback the changes.
Print a message or raise a warning for the first case, and raise an exception to rollback the changes.
You can see in the first screenshot that the usp_cashbook3 procedure also allows saving changes and deleting/adding rows accordingly.
You can disable this behavior using the DoNotSave and ProtectRows handlers as shown below for the view_cashbook3 view:
Context Parameters
Cell change handlers can use the following context parameters:
Parameter Name | Value |
---|---|
@DataLanguage or @data_language | A data language code selected using the Options dialog box |
@TableName or @table_name | A qualified name of the active query object like [s02].[usp_cashbook] |
@ColumnName or @column_name | The active column name |
@CellValue or @cell_value | The active cell text value |
@CellNumberValue or @cell_number_value | The active cell number value |
@CellDateTimeValue or @cell_datetime_value | The active cell datetime value |
@ChangedCellCount or @changed_cell_count | The total number of changed cells in the current transaction |
@ChangedCellIndex or @changed_cell_index | A cell index in the changed cell set starting 1 |
@TransactionID or @transaction_id | A unique GUID of the current transaction |
@TransactionStartTime or @transaction_start_time | A UTC start time of the current transaction |
@WindowsUserName or @windows_user_name | The Windows login of the current user |