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.
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.