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 may use a handler configuration table like xls.handlers or a view to link handlers.

For example:

In this sample, you may 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 may 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 may see in the first screenshot that the usp_cashbook3 procedure also allows saving changes and deleting/adding rows accordingly.

You may 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: