1

I want to export some data from SQL Server table after update to Firebird table using odbc. Is there way to use trigger? If yes that how to because I don't know how to use ODBC connection directly in trigger definition? I would like to parse some information from a table in SQL Server to assign them to the table in FireBird. For example, cut a DATETIME to the date and time and put them into two columns.

insict
  • 861
  • 2
  • 11
  • 19

1 Answers1

2

This definitely is possible but you can't use an ODBC connection directly within the trigger. Triggers have very limited functionality. They're typically just a special type of stored procedure.

http://technet.microsoft.com/en-us/library/ms189799.aspx

Typically the trigger will execute additional SQL but can also execute a system call. You could have your trigger call an executable which established an ODBC connection to your sql server database and sent the updates to firebird. Use a small java or C++ program to establish your ODBC connection or use something like JDBC/JTDS. JTDS allows for easy SSO access to sql server databases.

rdmcfee
  • 540
  • 6
  • 13
  • Thanks for answer. I Have additional question, can I use trigger to execute *.jar file with SQL parse script? – insict Sep 25 '13 at 10:28
  • Yes you can. You can use it to execute a windows shell command to call the jar file, or you can wrap your jar in an exe using a wrapper like launch4j http://launch4j.sourceforge.net/. If you're using ODBC I would recommend using a wrapper like Launch4j because it allows for better control of what JRE gets used. ODBC connections can be highly dependent on architecture. For example there is no 64 bit ODBC driver for MS Access and your JRE achitecture must match that of the ODBC driver so you would have to force your JAR to run on a 32 bit JRE. – rdmcfee Sep 25 '13 at 21:55
  • There are varying opinions on this of course: http://stackoverflow.com/questions/12974832/sql-server-can-i-use-exec-to-run-an-external-application – rdmcfee Sep 25 '13 at 21:58