topic Re: SQL Trigger on ArcSDE calling Python script in Data Management Questions

SQL Trigger on ArcSDE calling Python script

JanetRogers — Mon, 22 Nov 2010 03:41:42 GMT

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.

Re: SQL Trigger on ArcSDE calling Python script

JanetRogers — Tue, 23 Nov 2010 20:33:57 GMT

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?

Re: SQL Trigger on ArcSDE calling Python script

VinceAngelo — Tue, 23 Nov 2010 21:12:31 GMT

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

Re: SQL Trigger on ArcSDE calling Python script

JanetRogers — Tue, 23 Nov 2010 21:55:28 GMT

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.

Re: SQL Trigger on ArcSDE calling Python script

VinceAngelo — Tue, 23 Nov 2010 22:15:24 GMT

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

- V

Re: SQL Trigger on ArcSDE calling Python script

JanetRogers — Tue, 23 Nov 2010 22:29:34 GMT

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

Re: SQL Trigger on ArcSDE calling Python script

VinceAngelo — Wed, 24 Nov 2010 01:00:39 GMT

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

Re: SQL Trigger on ArcSDE calling Python script

JanetRogers — Wed, 24 Nov 2010 01:38:27 GMT

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?

Re: SQL Trigger on ArcSDE calling Python script

VinceAngelo — Wed, 24 Nov 2010 11:26:36 GMT

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

Re: SQL Trigger on ArcSDE calling Python script

justinperez — Wed, 14 Sep 2011 03:50:46 GMT

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.

Re: SQL Trigger on ArcSDE calling Python script

VinceAngelo — Wed, 14 Sep 2011 10:16:59 GMT

UPDATE and INSERT events don't occur when a versioned table is edited (both are modeled
as INSERTs to the Adds tables; UPDATEs also INSERT to the Deletes table). When reconciied,
compressed, and posted, the changes are implemented as an INSERT (or DELETE then INSERT
in the case of an UPDATE), but this usually occurs *much* later than the original edit.

You might experiment with triggers on the Adds table, but that is unsupported (and outside the
scope of this thread).

- V

Re: SQL Trigger on ArcSDE calling Python script

justinperez — Thu, 15 Sep 2011 02:33:14 GMT

I need to have an insert/update date applied to a record when the record is inserted/updated via Feature Services on SQL Server 2008 and ArcGIS Server 10. The records, ideally, would be from a versioned feature class. I thought this would be possible with a db trigger. I am hearing from other spots on the web that this will not be possible with versioned feature classes. After I get update/insert dates I will then have a python script do a gp.featurecompare to the original data to get changes whether attribute wise or spatial.

Otherwise maybe I could put a trigger on the add/delete tables (for each versioned feature class..correct?) and right the records out to a non-sde table.

Which tables are the add/delete tables in the SQL Server 2008 db?

Thanks.