Problem solve Get help with specific problems with your technologies, process and projects.

Writing JOIN queries between two databases

Is it possible to write a join query between two databases' tables? Shall we use beginTrans and commitTrans in...

the trigger, and if not, why?

The ability to write join queries between tables from different "databases" really depends on your DBMS. For most, the answer is yes. I recommend referring to your particular vendor's documentation. My responses to previous questions, Referencing a table in database A from database B and Selecting and comparing data from two different databases deal with this issue; you might find them helpful.

Regarding your second question, I will assume you are using MS SQL Server. In SQL Server, a trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. If an exception occurs in the trigger code, the DML statement that invoked the trigger, as well as any trigger DML which occurred before the exception, are rolled back. With that in mind, you don't need to explicitly begin and commit a transaction within a trigger.

However, a situation where you should have transaction control statements in a trigger would be in a case where you would begin a nested transaction in order to conditionally rollback some of the trigger's DML. In this case, I would recommend reading the section on Nested Transactions in the SQL Server documentation.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in February 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.