[SOLVED] SQL trigger to start Filemaker Script Trigger

[meryl]

Hi all!

I have a live connection between a Filemaker DB hosted on FMGateway and a SQL DB on another remote server. The layout of the SQL files in the Filemaker DB does not add new records to the Filemaker view of the database automatically as they are created in SQL.  After researching as best as I could,  I think I need the SQL code and how and where to write it in SQL (I'm obviously not a SQL programmer) that will send a trigger to activate a script trigger in Filemaker 10 to refresh the page.  The only other way I know to do this is to have a script run constantly that would refresh, search, or do something to get Filemaker to check for any new records in SQL.  I know this would not be a great idea at all.

Could somebody get me going in the right direction or give me a better idea?

Blessings,

Meryl Gullings
Dedicated Database Solutions

[Michael Petrov]

Hi Meryl,

This is actually quite a complex question, your best bet is probably to somehow trigger an XML request (through an FMStudio PHP page or something) that would edit a real record in FM - which in turn fires the trigger. The main issue is triggering the PHP page from SQL, which fully depends on the database engine that you are using. I would search for your database engine and the keywords "http request from stored procedure" - typically triggers in SQL are written as stored procedures and the capabilities of hte stored procedures depend on the SQL server itself.

Best Regards,
Michael Petrov

[meryl]

Hi Michael,

Thank you for the quick response!

After reading your post, I wondered if, after the SQL transaction is complete, I put a redirect at the end of the web page just before the "Thank You"  web page of the SQL powered site that would go to a web page linked through FMStudio to my Filemaker DB.  This page would be hosted on FMGateway with the rest of the Website that I have created.  This new PHP page would run the script in Filemaker that refreshes the SQL table in Filemaker and creates a new record in the Order table.  My design is for the new Filemaker Solution I have developed be the master for keeping track of the inventory and showing all sales in the original SQL website and the new Filemaker powered website.  After starting that Filemaker script, it would redirect the SQL customer back to the SQL Thank You page.  Hopefully the customer would never notice.

I would feel much more comfortable with attempting something along this line rather than messing with SQL code which I know nothing about... yet.

Blessings,

Meryl

Dedicated Database Solutions

[meryl]

Hi Michael,

That really makes sense.  The original SQL website is written in Cold Fusion so my research tells me I could use cURL in there as well.  Do I understand this procedure correctly, the user would never leave the page, cURL would just call my PHP page that would run the script, then the user would go directly to the Thank You page?  If so, wouldn't it be even better to run this from the Thank You page?

Blessings,

Meryl
DDS

[meryl]

Hi Michael,

I wanted to thank you and post one more reply on this string to let future users know how I was able to resolve this.

Apparantely my research wasn't up to par, because my understanding now is that instead of using cURL in Coldfusion,  you would use ColdFusion code to do the same thing.  You would call a URL in Coldfusion.  My friend and Coldfusion expert helped me.  I'm not sure of the code exactly, but if anybody needs it, email me and I will be glad to get it for you.  from the SQL website "Thank You" page it calls a PHP URL in my Filemaker DB which runs the script to create a new record and then I used the SetField script command for every piece of information I needed to bring into that new order from SQL.  The customer from the SQL site never notices.

Thank you Michael!

Blessings,

Meryl
Dedicated Database Solutions
meryl@dedicateddbsolutions.com