Skip to content

On Save Stored Procedures

When Editable Table records are added, edited or deleted, you can wire up a set of stored procs to run on the Before Save event and the After Save Event:

Stored Procedure Parameters

A custom stored procedure used here will be passed one argument, @json. This is a packet of JSON that represents what the event is working on and has the following structure:

{
    action: "",
    catalog: "",
    datasource: "">,
    deletes: [{columnName: "value"}],
    editableTableId: 0,
    editableTableName: "",
    eventType: "",
    inserts: [{columnName: "value"}],
    serverTimeLocal: "",
    serverTimeUTC: "",
    updates: [{columnName: "value"}],
    userId: ""
}
Property Type Description
action string the Table Editor Action being performed - One of Create or Update or Delete>
catalog string the name of the database that is being used
datasource string the name of the datasource that is being used
deletes array the array of records being deleted
editableTableId int the database ID of the Editable Table that caused the event
editableTableName string the name of the Editable Table that caused the event
eventType string the Table Editor onSave event being triggered - One of AfterSave or BeforeSave
inserts array the array of records being created
serverTimeLocal string The server local time of the event in ISO 8601 Notation (including timezone offset)
serverTimeUTC string The UTC time of the event in ISO 8601 Notation
updates array the array of records being updated
userId string the login id of the user performing the action from the CALUMOUsers table

You should use the following signature for your stored procedures:

Example

CREATE PROC <name>
    @json varchar(max)
AS
 
GO

Stored Procedure Return

Inside custom stored procedures, there are ways to feedback to your users about what has taken place.

Success Message

Only valid for AfterSave events, return a message to users to let them know the action was successful

SELECT 'AWESOME - It Worked!!'

Throw an Error

Use the T-SQL THROW directive without a following CATCH block to send an error back to the user and terminate the action.

throw 51000, 'Could not validate the customer', 1

RAISERROR

Use the T-SQL RAISERROR directive to send an error back to the user and terminate the action.

RAISERROR('Could not validate the customer', 16, 1)

Simple Error Return

Use a simple select statement in the following format to send an error back to the user and terminate the action.

SELECT 'ERROR', 'This is the error to show the user'

Cell By Cell Error Return

Create a data table result that returns:

Column Number Detail
COLUMN 1      KEY of the record in error
COLUMN 2    Column name on the record that has the error
COLUMN 2      Error to return for this KEY/Column combination

This will appear as cell errors in the correct record and column.

SELECT 1029, 'CustomerName', 'The customer name must not contain the # character'
UNION ALL Select 1029, 'CustomerState', 'The customer state must not be empty'
UNION ALL Select 1029, 'CustomerLicenseCount', 'The license count must be greater than 0'