Here is Frank Zamani comments on how it can be implemented in Caspio Bridge:
"This has been done before. The process is straight forward:
The trigger in step 2 is created by the customer and provided to us or a Caspio engineer builds it for the customer for a small fee."
- A web form is used to enter the starting date and the number of hours the project requires.
- Upon submission, a database trigger (custom SQL code) uses the customer’s lookup tables of holidays and working hours, and comes up with the end date, storing it on the just-inserted record.
- The web form from step 1 loads a "Search and Report Data Page" against the record that was just submitted and displays the calculated date.
In case you do not have an idea on how SQL trigger looks like, here is rough draft of one:
-- Author: Caspio, Inc.
-- Create date: September 3, 2009
-- Description: Calculates end date based on start date and number of hours
-- for a project.
-- Replace <MyTable>, <FromDateField>, <ProjectHoursField>,
-- <EndDateField> with the appropriate field names from your table
CREATE TRIGGER [dbo].[ct_Ins_Upd_CalcDates]
AFTER INSERT, UPDATE
SET NOCOUNT ON;
DECLARE @PKID int,
SELECT @PKID = PK_ID,
@FromDate_local = ISNULL(<FromDateField>,'01/01/1900'),
@projectHours_local = ISNULL(<ProjectHoursField>,0)
IF @FromDate_local <> '1/1/1900'
SELECT @EndDate_local = fn_GetEndDate(@FromDate_local, @projectHours_local)
SET <EndDateField> = @EndDate_local
WHERE PK_ID = @PKID
Following clarifications should help you better understand the process of building and using such a trigger.
Which name conversions are used to reference tables and columns from the app?
If some changes were done in the app we need to change this trigger too every time it happens?
When this trigger is created by the customer is there any tools to test or debug it from customers’ side?
"Users can run their SQL code against their account via our API to test but we don’t provide a development environment."
Caspio Bridge’s capability of executing customer’s own code on server using SQL triggers opens wide horizons for implementing complex business logic in applications. The only problem is that you should be quite familiar with SQL in order to use it to the full extent. There also one more point that draws my attention: though I don’t know how data isolation is implemented in Caspio, but I think that possibility of running own code on server could present higher security risk of unauthorized data access.