Tables in Remote Databases

You cannot create triggers on tables or views that reside outside the current database. You can, however, define a trigger on a local table whose trigger action manipulates a table in another database of the local server instance, or a table in a database of another server instance.

The following example defines an Update trigger on the newtab table in the current database of the local HCL OneDB™ server instance, dbserver1, to which the session is connected. Here the trigger action specifies an UPDATE operation on the items table of the stores_demo database of the remote dbserver2 HCL OneDB server instance:

CREATE TRIGGER upd_nt UPDATE ON newtab
   REFERENCING NEW AS post
   FOR EACH ROW(UPDATE stores_demo@dbserver2:items 
      SET quantity = post.qty WHERE stock_num = post.stock 
      AND manu_code = post.mc);

In summary, triggers registered in the local database can support local, cross-database, and cross-server trigger actions:

local trigger actions on a table in the local database
cross-database trigger actions on a table in another database of the local server instance
cross-server trigger actions on a table in a database of a remote server instance.

The cross-server triggered action of a trigger that is defined in a database of a remote server instance can be the event that activates one or more triggers in the local database, but in this case, triggered actions of the local trigger cannot be cross-server operations. If a SELECT, DELETE, INSERT, MERGE, or UPDATE statement from a remote database server is the event that activates a local trigger whose action specifies a table in a database of a remote server instance, the trigger actions fail.

For example, the following combination of trigger action and triggering statement results in an error when the triggering statement executes:

-- Trigger action from dbserver1 to dbserver3:
CREATE TRIGGER upd_nt UPDATE ON newtab
   REFERENCING NEW AS post
   FOR EACH ROW(UPDATE stores_demo@dbserver3:items 
      SET quantity = post.qty WHERE stock_num = post.stock 
      AND manu_code = post.mc);

-- Triggering statement from dbserver2:
UPDATE stores_demo@dbserver1:newtab 
   SET qty = qty * 2 WHERE s_num = 5 
   AND mc = 'ANZ';

The UPDATE statement above returns an error at run time, because a cross-server triggering event cannot trigger another cross-server action.

Important: As a security precaution, discretionary access privileges that a user holds only from a role cannot provide access to tables outside the current database through a view or through a trigger. Cross-database trigger actions and cross-server trigger actions require access privileges on the non-local database and table that were granted directly to the user, or granted to the PUBLIC group.