John

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

If not it would make it a trifle more difficult

>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.

It doesn't matter if its in a transaction or not (I think) since with two tables there will be two triggers firing. Insert not a problem because of the nice handy NULL (washes mouth out) but with update its different. If only one table is updated again no problem but if both are updated then the external program will be fired in Table1's trigger and then in Table2's trigger since in both cases all three fields will be filled.

It can get even more fun - you enter all three fields post, external app runs, alter one field, external app runs, alter field back to the old value - you guessed it - external app runs. I have no idea if this is acceptable behaviour.

I've been down this road once or twice and its a bit of a minefield unless planned out very carefully first.

>Maybe I am solving a different problem

Dunno - you might be.


Roy Lambert [Team Elevate]

Roy

> It doesn't matter if its in a transaction or not (I think) since with two tables there will be two triggers firing.
Insert not a problem because of the nice handy NULL (washes mouth out) but with update its different. If only one table
is updated again no problem but if both are updated then the external program will be fired in Table1's trigger and then
in Table2's trigger since in both cases all three fields will be filled.
>
> It can get even more fun - you enter all three fields post, external app runs, alter one field, external app runs,
alter field back to the old value - you guessed it - external app runs. I have no idea if this is acceptable behaviour.
>

OK - I was taking the OP at his word ("Would it be possible to somehow trigger an external program to run once these
were all added?") that the external routine was to be run each time the state changed from prerequisites not met to
prerequisites met.  In this case I still don't think it matters whether you have one table being updated or two.

John

John

>OK - I was taking the OP at his word ("Would it be possible to somehow trigger an external program to run once these
>were all added?") that the external routine was to be run each time the state changed from prerequisites not met to
>prerequisites met. In this case I still don't think it matters whether you have one table being updated or two.

I've asked a few questions that haven't been answered yet so who knows?

Roy Lambert [Team Elevate]

Roy

<< 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? >>

Yes, at first I'd only like it to run when all three prerequisites were met, but afterward, I'd like it to run anytime any of the three prerequisites were updated.

Roy

<< 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. >>

Ok, after giving the matter more thought, here are the specifics (Sorry for making it a bit more difficult/changing direction a bit):

All prerequisite columns have add and change timestamps associated with them.

Table1 has data for 2 unique id's I want to monitor, and I want to monitor for fill/update in the same column for both rows. Table2 has data for 2 unique id's I want to monitor, and I want to monitor for fill/update in the same column for both rows. Table3 has data for 2 unique id's I want to monitor, and I want to monitor for fill/update of timestamps.

Ex:

Table1
UIDColumn ... ColumnToBeMonitored   AddTimeStamp   ChangeTimeStamp
123                5068.32                        42658972.12      46236589.36
1456              7982.26                        41569875.25      43256872.87

Table2
UIDColumn ... ColumnToBeMonitored   AddTimeStamp   ChangeTimeStamp
234                4956.65                        42569872.69       44569875.39
1659              45985687.26                 40365698.78       43659874.47

Table3
UIDColumn ... AddTimeStamp   ChangeTimeStamp
345                46598772.54       48756983.65
652                42356897.23       47852159.42

At first, I would only like the program to run when Table1's column to be monitored is filled for each of it's two specific UID's I need, Table2's column to be monitored is filled for each of it's two specific UID's I need, and Table3's addtimestamp is filled for each of it's two specific UID's I need.

Subsequently, I would like the program to run if Table1's column to be monitored is updated for either of it's two specific UID's I need, Table2's column to be monitored is updated for either of it's two specific UID's I need, or Table3's changetimestamp is updated for either of it's two specific UID's I need. (Basically if anything is updated)

diablo908


That is totally different (and a lot more complex . Depending on wether you prefer to work in Delphi or SQL your start point is either develop a procedure in Delphi or a script in SQL to carry out the whole test for each condition (opening all the tables etc). Once you have that working satisfactorily then and only then move on to the stage of building it into a trigger.

Before I say anything more - the unique IDS are they fixed or changeable ie for Table1 are they ALWAYS 123 1456. If not fixed then there is another layer of complexity.

Roy Lambert [Team Elevate]

Always fixed

diablo908


OK Stage 1. Assuming you use Delphi can you write a small Delphi program that will test the rows and columns you're interested in and call the external program to prove out your logic? If not you're stuffed if so then it can be used in the trigger as an external program, or function. You might as well do the testing in Delphi since you can't SehellExecute out from SQL (unless its an enhancement I've missed)

Roy Lambert