Q

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 first published in February 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close