2

I want to create an insert trigger on MySQL which will automatically insert the record into an Oracle database. I would like to know if there are people that have experience to share on this topic.

Cheers

Improve this question
2
  • I doubt this would ever work - mysql knows nothing about oracle and vice versa Commented Sep 7, 2012 at 2:27
  • Thank you Adrian. Do you have any recommendation about synchronizing data across these two different databases? Commented Sep 9, 2012 at 12:14

1 Answer 1

Reset to default
1

Invoke a script as is done in this example that calls the Oracle code.

Note: you lose support for transactions (there will be no built-in rollback for the Oracle database) when you perform this type of cascading, and you also will take a likely very large performance hit in doing so. The script could turn around and simply call Java code or some other executable that invokes your some generic code to insert into Oracle, or it could be a raw query that gets passed arguments from the script.

This is almost certainly a bad idea because of the odd side-effect behavior, but it's one that can be implemented. I think that you would be much better off having the code to do this against two different DataSources (in Java/.NET speak) rather than have a hidden script in a MySQL trigger that screams unmaintainable, as well as hidden failure for future developers.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

You can get a trigger to call a script - did not know that
I think it only really makes sense in rare, kick-off-some-process circumstances like the one in the example (processing on the first of the month). Beyond that, I think the opportunity for abuse may exceed the value of it.
Especially since mysql now has an event scheduler
You are right pickypg. This issue I'm worrying is the possible hidden failure. But because the update of the Mysql database is conducted only by one end user, there will be less performance issues. In addition, the example script you provided is for Mysql, not for Oracle. I will be appreciate if you could provide an Oracle example.
@sounder The easiest way would be to install lib_mysql_udfsys (noting its words of caution), and then literally invoking a bash script that logs into Oracle and then executes the INSERT command via the trigger as a function call like CALL sys_eval('/path/to/script.sh arg1 arg2 arg3', sysout_of_oracle).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.