SQL Reference Guide

Cross Database Query

FairCom DB SQL allows opening multiple local database from the same server. Databases are opened automatically when referenced in a SQL statement, and the same credentials are used for all databases. To access a table in a database other than the default, table name is qualified with a database name and a user name: <dbname>.<owner>.<tablename>. Tables in database other than the default database must be fully qualified, that is both the database name and the owner name must be specified.

ISQL> select * from table1, database2.owner.table2;

The execution of triggers takes place in the context of the table on which the trigger resides. Therefore, an insert into a table in the default database will execute a trigger on that table in the context of the default database. An insert into a table in a non-default database, will execute a trigger on that table in the context of the non-default database.

Trigger Execution

In the following example, identical tables with identical triggers are created in two different databases. The trigger does three inserts, to fully specified tables in each database, and to a table without a database specification.

create table trig1 (f1 int);

create table trigtbl (f1 char(25));

create trigger trigibs BEFORE INSERT on trig1

IMPORT

import common.classes.*;

BEGIN

SQLIStatement insert_tst1 = new SQLIStatement ( DharmaUtility.doTransform("INSERT INTO db2.systpe.trigtbl values ('db2 INS stmt trig')"));

insert_tst1.execute();

SQLIStatement insert_tst2 = new SQLIStatement ( DharmaUtility.doTransform("INSERT INTO db1.systpe.trigtbl values ('db1 INS stmt trig')"));

insert_tst2.execute();

SQLIStatement insert_tst3 = new SQLIStatement ( DharmaUtility.doTransform("INSERT INTO trigtbl values ('local INS stmt trig')"));

insert_tst3.execute();

END

commit work;

c:\isql -s schema.sql db1

c:\isql -s schema.sql db2

In the first test, we insert into the table in the default database. The non-qualified insert from the trigger goes into the table in the default database.

ISQL> insert into trig1 values (1);

ISQL> select * from db1.systpe.trigtbl;

db1 INS stmt trig

local INS stmt trig

2 records selected

ISQL> select * from db2.systpe.trigtbl;

db2 INS stmt trig

1 record selected

ISQL> rollback work;

ISQL> quit;

In the second test, we insert into the table in the non-default database. The non-qualified insert from the trigger goes into the table in the non-default database.

ISQL> insert into db2.systpe.trig1 values (1);

1 record inserted.

ISQL> select * from trigtbl;