Trigger a python script to run after a row is added to the feature class

Question

I have a Python script which runs on a SDE feature class daily (as a scheduled task). I would actually like this script to run whenever a new row is added to the feature class (data is captured in the field using a mobile device and posted to the server).

I was considering using a trigger as mentioned in this question as my data is not versioned, but I do not want to overcomplicate things if there is an inbuilt SDE tool which can achieve this or something similar. The data is currently stored in ArcSDE 10/SQL Server 2008, but I do have ArcSDE 10.1/SQL Server 2012 available.

Currently when the script runs, it checks for NULL values in a certain field. If it finds it, it selects those rows and performs several geoprocessing operations on them. It then populates that field with a value to show that everything has been processed.

While I have a lot of experience editing/reconciling/posting/versioning SDE data, I only have limited experience with the database/admin side of it (assigning privileges, basic dabbling in SQL management studio), with a basic understanding of the underlying architecture.

Answer 1

First of all: No, SDE does not have any built in feature of event triggered processing. As far as I know you can probably do something working with the CASE Tools where you can implement your own types of featureclasses with their own behaviour, but that means ArcObjects programming.

I think database triggers can be used for this, and this wouldn't be too complicated, but as mentioned here calling a rather big piece of software (like a python/arcpy script) out of a database trigger (meant to be minimalistic) brings you a couple of things, you should at least think of:

1. Every INSERT fires the trigger. So 5 rows inserted means that in the worst case, your python script is started 5 times. You possibly have to prevent that by some mechanism.
2. Can you solve the task of filling those NULL Values natively in SQL? Then this would be done much more efficient, because SQL Server would manage concurrency issues.
3. If you really need to run a Python script, then on which machine is the Interpreter running? On the database server means, that you can call it with a system call out of that trigger. If its on a remote machine, you probably need ArcGIS Server to publish the script as a geoprocessing tool and call it via http (asynchron).
4. As mentioned in 1. you need a mechanism that ensures, that the script only runs when it really should run.

I guess the best will be, to keep your script running periodically, but to shorten the time period to only some minutes. Add a table where you can store flags which indicate a change in your Featureclass. Use a database Trigger to set the "changed" flag. Modify your script to run only if the changed flag is set, or if not then simply quit.

This way, your script will fill your data in short periods of time without running into raceconditions.

You can read here how you activate and use system calls in sql server triggers (although the example is for PHP, but thats just anoter interpreter).

Also read through all disadvantages the authors are mentioning.