INSTEAD OF UPDATE trigger fetches the complete view.
officialdharam Jun 16, 2015 6:26 AMI have a View backed by a REST API which returns a collection. Here is the view definition
CREATE VIEW ContactView( id varchar(100), type varchar(100), title varchar(100), firstName varchar(100), lastName varchar(100), name varchar(100), emailAddress varchar(100), accountName varchar(100), currentStatus varchar(100), businessPhone varchar(100), province varchar(100), postalCode varchar(100), country varchar(100), subscriptionDate varchar(100), updatedAt varchar(100), depth varchar(100), isBounceback varchar(100), isSubscribed varchar(100), salesPerson varchar(100) ) OPTIONS (UPDATABLE TRUE) AS SELECT A.id, A.type, A.title, A.firstName, A.lastName, A.name, A.emailAddress, A.accountName, A.currentStatus, A.businessPhone, A.province, A.postalCode, A.country, A.subscriptionDate, A.updatedAt, A.depth, A.isBounceback, A.isSubscribed, A.salesPerson FROM (EXEC CustomerSource.invokeHttp('GET', null, 'https://secure.eloqua.com/API/REST/1.0/data/contacts?depth=complete', 'TRUE')) AS f, XMLTABLE('/holder/elements' PASSING JSONTOXML('holder', f.result) COLUMNS id string PATH 'id/text()', type string PATH 'type/text()', title string PATH 'title/text()', firstName string PATH 'firstName/text()', lastName string PATH 'type/text()', name string PATH 'name/text()', emailAddress string PATH 'emailAddress/text()', accountName string PATH 'accountName/text()', currentStatus string PATH 'currentStatus/text()', businessPhone string PATH 'businessPhone/text()', province string PATH 'province/text()', postalCode string PATH 'postalCode/text()', country string PATH 'country/text()', subscriptionDate string PATH 'subscriptionDate/text()', updatedAt string PATH 'updatedAt/text()', depth string PATH 'depth/text()', isBounceback string PATH 'isBounceback/text()', isSubscribed string PATH 'isSubscribed/text()', salesPerson string PATH 'salesPerson/text()' ) AS A;
I am able to implement a INSTEAD OF INSERT trigger on the view and invoke virtual procedure, which in turn makes a POST call to the REST web service. Here is the trigger and the procedure which is successful.
CREATE VIRTUAL PROCEDURE CreateContact( type varchar, title varchar, firstName varchar, lastName varchar, name varchar, emailAddress varchar, accountName varchar, businessPhone varchar, province varchar, postalCode varchar, country varchar, salesPerson varchar ) RETURNS (id varchar(25), name varchar(25)) AS BEGIN DECLARE string url = 'https://secure.eloqua.com/API/REST/1.0/data/contact'; DECLARE clob obj = jsonObject(type as type, title as title, firstName as firstName, lastName as lastName, name as name, emailAddress as emailAddress, accountName as accountName, businessPhone as businessPhone, province as province, postalCode as postalCode, country as country, salesPerson as salesPerson); SELECT B.id as id, B.name as name FROM (call CustomerSource.invokeHttp('POST', obj , url, 'TRUE', '{"Content-Type":["application/json"]}')) AS f, XMLTABLE('/holder' PASSING JSONTOXML('holder', f.result) COLUMNS id string PATH 'id/text()', name string PATH 'name/text()' ) AS B; END; CREATE TRIGGER ON ContactView INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC call CreateContact.CreateContact( New.type, New.title, New.firstName, New.lastName, New.name, New.emailAddress, New.accountName, New.businessPhone, New.province, New.postalCode, New.country, New.salesPerson); END;
Now, I tried to implement the INSTEAD OF UPDATE trigger on the view using a different virtual procedure, which will make a PUT call to the REST web service. Upon writing this and deploying the VDB, I realized that the UPDATE JDBC calls on the view are actually making a GET call to fetch the records from the REST API. This times out because the API has a lot of data. The below one doesn't work:
CREATE VIRTUAL PROCEDURE UpdateContact( id varchar, type varchar, title varchar, firstName varchar, lastName varchar, name varchar, emailAddress varchar, accountName varchar, businessPhone varchar, province varchar, postalCode varchar, country varchar, salesPerson varchar ) RETURNS (id varchar(25), name varchar(25)) AS BEGIN DECLARE string url = CONCAT('https://secure.eloqua.com/API/REST/1.0/data/contact/',id); DECLARE clob obj = jsonObject(id as id, type as type, title as title, firstName as firstName, lastName as lastName, name as name, emailAddress as emailAddress, accountName as accountName, businessPhone as businessPhone, province as province, postalCode as postalCode, country as country, salesPerson as salesPerson); SELECT B.id as id, B.name as name FROM (call CustomerSource.invokeHttp('PUT', obj , url, 'TRUE', '{"Content-Type":["application/json"]}')) AS f, XMLTABLE('/holder' PASSING JSONTOXML('holder', f.result) COLUMNS id string PATH 'id/text()', name string PATH 'name/text()' ) AS B; END; CREATE TRIGGER ON ContactView INSTEAD OF UPDATE AS FOR EACH ROW BEGIN ATOMIC call CreateContact.UpdateContact( New.id, New.type, New.title, New.firstName, New.lastName, New.name, New.emailAddress, New.accountName, New.businessPhone, New.province, New.postalCode, New.country, New.salesPerson); END;
Please suggest if I am missing something.