Is it possible to somehow trigger an external program to run if a set of prerequisite columns is added/updated in a database?

Say I had three columns, Table1.ExA, Table 2.ExB, and Table2.ExC that were needed as inputs to an external program, and all three were inserted for a given item. Would it be possible to somehow trigger an external program to run once these were all added?

Also, is there some other alternate best practice to do something like this?

<< Say I had three columns, Table1.ExA, Table 2.ExB, and Table2.ExC that
were needed as inputs to an external program, and all three were inserted
for a given item. Would it be possible to somehow trigger an external
program to run once these were all added? >>

Sure, you would create a trigger that looks like this:

CREATE TRIGGER "CheckColumns" AFTER ALL ON "customer"
WHEN OPERATION='Insert' OR OPERATION='Update'
BEGIN
  IF NEWROW.Addr1 IS NOT NULL AND NEWROW.City IS NOT NULL AND  NEWROW.State
IS NOT NULL THEN
     CALL CallExternalApplication;  -- This is a stored procedure in an
external module that will call your external application
  END IF;
END

You'll need to create the external module and have it do the shelling out to
call the external application.  You can find out more information on that
here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=External_Modules

To create an external module in Delphi, just use the
File/New/Other/ElevateDB section and choose the ElevateDB External Module
option.  Follow the instructions in the source code for the main module unit
to handle the parameters, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I think it gets slightly more complex than your approach since there are two tables involved. If Table1 is always populated before Table2 (or vice versa) then it would be easier, if not you'd need two triggers - one for each table and check both tables in each. Also nothing is said about what should happen if data is altered. Should the external program only trigger on insert or can it also trigger on update.

A bit more info is needed.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Tim


I think it gets slightly more complex than your approach since there are two tables involved. If Table1 is always populated before Table2 (or vice versa) then it would be easier, if not you'd need two triggers - one for each table and check both tables in each. Also nothing is said about what should happen if data is altered. Should the external program only trigger on insert or can it also trigger on update.

A bit more info is needed.

Roy Lambert [Team Elevate]


Trigger on insert or update would be nice.

diablo908

>Trigger on insert or update would be nice.

OK the potential problem with update is the external program could be called twice - one from table1's trigger and the second time from table2's. Is that a problem?

Roy Lambert [Team Elevate]

I see the issue here... Would it be "better" if I set up something on a schedule instead of using triggers?

diablo908


>I see the issue here... Would it be "better" if I set up something on a schedule instead of using triggers?

No idea

I can only make comments that are informed by what you've told us and by taking a few wild guesses. Can you post a fuller description of what it is you're doing and what you're trying to achieve.

For now I'm guessing that running the external program twice for a given set of inputs would be a problem. However, I don't know if the records that these entries are posted into are pertinent or not. If not then one solution would be to have a third table which would be the one controlling the external program. It would hold the three values from the other tables and only be updated if there wasn't already a record containing all three values (ie a multipart key on the third table).

Another question: If Table1 is updated to X, then Table2 is updated, the external program runs. Then the value in Table1 is altered to a different value (X+1), the external program runs. Finally the value in Table1 is altered back to X, Should the external program run again or not?


Roy Lambert [Team Elevate]

Roy

> OK the potential problem with update is the external program could be called twice - one from table1's trigger and the
second time from table2's.

Are you sure?  If you are checking only when values (however many tables) go from nulll to filled and only firing the
external routine when the last one is filled surely that will only happen once?

John

John


>> OK the potential problem with update is the external program could be called twice - one from table1's trigger and the
>second time from table2's.
>
>Are you sure? If you are checking only when values (however many tables) go from nulll to filled and only firing the
>external routine when the last one is filled surely that will only happen once?

Dead right for insert, but not for update unless you can see something I can't

Roy Lambert [Team Elevate]

Roy

>
> Dead right for insert, but not for update unless you can see something I can't
>

I am presuming that there is a common id between the tables and OtherTableField is retrieved from a cursor.

The update part of the trigger I thought would be something like the following .  The insert part would leave out the
oldrow stuff.

IF (NEWROW.Addr1 IS NOT NULL AND NEWROW.City IS NOT NULL AND  OtherTableField
IS NOT NULL ) and (OLDROW.Addr1 IS NULL OR OLDROW.City IS  NULL)
THEN .......

OtherTable would have a mirrored trigger.  If you were concerned about both tables being updated in different sessions
at the same time I guess you would need to protect it with a transaction.

Maybe I am solving a different problem

John