Updates to ArcSDE via Oracle trigger

Question

Is it possible and/or feasible for an Oracle trigger to periodically update ArcSDE via a REST geoprocessing service?

Does anyone have any experience with such a thing?

The reason for this is that I have an Oracle database that is separate from the SDE database, and there are number of business rules that we want to run first, before inserting the geometry into SDE.

So the basic workflow would be:
1. Point features via ArcGIS Server via ArcSDE loaded onto web map
2. User moves point or edits attributes
3. Edits to points first get written to Oracle, then, via trigger to ArcSDE

Answer 1

Oracle database can access web-based services (incl. REST) via built-in UTL_HTTP package.
You formulate & set-up the request, send it and receive & process the response.

I don't have any experience with the package, but your workflow seems to be doable.

Update
The code could be like this:

DECLARE
  req   utl_http.req;
  resp  utl_http.resp;
  name  VARCHAR2(256);
  value VARCHAR2(1024);
BEGIN

  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');    

  req := utl_http.begin_request('http://www.my-company.com/rest/process');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);
  LOOP
    utl_http.read_line(resp, value, TRUE);
    dbms_output.put_line(value);
  END LOOP;
  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
END;

You just wrap this into you trigger.

Answer 2

Have you considered using ArcGIS Feature Services?

Answer 3

What database platform is the ArcSDE database hosted on ?

Using Oracle and SDO_GEOMETRY as the data type this could work with the workflow below, but you need to take over management of the layer yourself rather than letting ArcSDE do this for you otherwise ArcSDE will get confused by the direct updates to the business table.

As suggested by @ujjwalesri you may find it easier to use a Feature Service to edit the features directly, or investigating the use of Spatial Views if that is possible in your architecture.

Pre-requisites

• A SQL data type is used to store the geometries in the layer, for example SDO_GEOMETRY or ST_GEOMETRY. SDEBINARY will be difficult to work with as the geometry isn't stored in the business table for your layer so isn't easy to update with a trigger.
• ArcSDE Layer is registered with USER maintaining OBJECTID column (see http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/datamgmt/sdelayer.htm), or
• The layer is made available thorugh ArcGIS as a query layer.

Update Workflow

1. Data update on Oracle database, fires trigger to update geometry
2. Trigger updates/inserts deletes on business table for layer in remote SDE database
3. ArcSDE sees updated records in layer

If you are wishing to make these kind of direct updates to layers from the database side then you are really only using ArcSDE like a viewer interface to see the spatial data held in your database tables. Once you start updating spatial information directly from the database then it's no longer trivial to edit this using the ArcGIS/ArcSDE versioning and editing stack.