SQL Trigger on ArcSDE calling Python script

[JanetRogers]

I have searched everywhere for this information, but I have not been able to come up with an answer.  I do not have ArcSDE working so I cannot test this.

I need to run a script from a TRIGGER in SQL on an ArcSDE server.  I know that I can call java or C, however I would like to run a python script.  Is this possible?  Are there any contingencies (eg extra installs required on the server)?

The current install of SQL Server is 2005.

[JanetRogers]

The lack of response appears to indicate that Python cannot be called from a SQL Trigger.  In that case,  is anyone able to tell me what sort of files can be?  Is it only java and C?

[VinceAngelo]

What do you want the trigger to do? How many feature are involved in the table?
How often would the trigger be executed?

Generally speaking, triggers should be *very* light applications -- if it can't be
executed in a few milliseconds (ideally, certainly under a second per event),
you probably should look at using a different mechanism.

- V

[JanetRogers]

The trigger needs to copy a shape feature from  table one to table two.  This occurs when an INSERT is made to table one.  The trigger fires on an insert, just haven't been able to write the code that does the copy yet.

My client is adamant the copy is to occur by trigger.

[VinceAngelo]

It's not possible with SQL-Server 2005, but it's *very* easy with SQL-Server 2008
using GEOMETRY types.

- V

[JanetRogers]

I know that.   Only trouble is client has 2005 and won't be ungrading any time soon.

[VinceAngelo]

Then your only options are:
1) Have the trigger write data to a file, then signal an application
    to execute the instructions in the file (or if the data is small
    enough, just signal with the data).
2) Re-architect the database so that the trigger can populate a
    row which participates in a view to re-use the source geometry.

If you have a developer skilled in interprocess communications,
solution 1 may only take a week or two to implement (mostly
ArcSDE 'C' API spin-up time).  If concurrency isn't an issue,
then a frequently-running application could execute the inserts
(certainly for prototype purposes); I could code that app in
days, but I have 20 years experience in real-time application
development, 15 years with the ArcSDE 'C' API, and a very deep
bag of tricks (aka 'se_toolkit') to speed development.

It's a shame, since this could be trivially accomplished with any
RDBMS that wasn't SQL-Server 2005 (2008, Oracle, Informix,
DB2, or PostgreSQL).

- V

[JanetRogers]

Yes I am aware of all that.  I am currently working with a java script to do the work for me. Thanks for your estimates though, now I know that getting it done in a day or so (client preference0 is not feasible.

However this does bring me back to the original question - can I only call java and C from a SQL Trigger or is it possible to call Python or other types of files?

[VinceAngelo]

If you can get to C or Java, you can get to anything, but that doesn't mean you should.
Using Python inside a trigger is a bit like using a 737 to deliver mail in the bush -- using
ArcPy is more like an Airbus 380.

The key to a good trigger is minimalism. You can leave the large clunky app running
outside the database, where the initialization and termination costs can be amortized
across the application life, and then put the effort into transmitting and queuing the
data messages, so that a bulk insert doesn't cripple your server with backlog (the best
bet on the server side is a multi-threaded architecture that permits one queuing agent
to accept messages in milliseconds, and one or more worker threads to act on the
requests -- make sure you have a way to preserve unsatisfied requests in the event
of system shutdown before they are completed, and a recovery protocol to restore
stability in the event of message loss).

A day or two might suffice, if you have a seasoned team of six developers ready to
pull a double all-nighter 😉

- V

[justinperez]

How would I build a trigger that lets me write to a date field when a insert or update occurs on a versioned feature class?  I was told that it wouldn't work on a versioned feature class.  Thanks.