How do I call a script when a table's data changes in Sybase 12.5?

Question

OS is UNIX. I'd like to call a shell script to do external validation when the data in a table changes. Is it possible, and if so how.

Many thanks.

---

Updated: Ideally, I would like to call my external script once for any discrete operation: i.e.

• update... > call script once
• insert into... > call script once
• bcp into > call script once

Answer 1

I think you could call xp_cmdshell from within a trigger.

---

Edit: As mentioned in the comments, a trigger fires only once per data modification statement. (See books online.) So if you have an update statement that affects 100 rows, the trigger will only fire once, not 100 times. That should take care of your first two points (update, insert into).

A trigger won't fire on bcp. But bcp should happen from the command line so not sure why you wouldn't just add your shell script after every bcp command?

There's some more information on calling xp_cmdshell from a trigger here.

Some further comments: You haven't really described your environment or what you're trying to accomplish. If you're in an OLTP environment you'll have to make sure that your shell script will work OK if multiple updates are happening to the table at once. If 3 users do updates at the same time will your script still work if three of them are running at once? If you're doing this as part of some kind of OLAP or batch system I'm not sure why you wouldn't add the shell script to the job flow.

Answer 2

Hi,

I have a similar situation, for any DML operation Insert/ update on a table in sybase it should trigger a curl command which invokes and triggers the DataPower.

Are you able to solve your issue with the xp_cmdshell command.

Please advice in this regard.

Thanks, Srini

Answer 3

Thanks for your reply.

Once I implement it, I will post the actual result.

Regards, Srini