DB2 trigger to Insert/Update records into different database-CodePudding

I want to create a trigger on one database's table and want to add that `records into another database's table. Let us suppose, I have one table on first database, which has 5 rows and 2 columns. Another side I have one table on another database, which has 3 rows and 2 columns, where 3 rows of another database's table are exact same as 3 rows of the first database's table.

I know, how to trigger the Insert/Update table on the same database. But how to trigger table from one database to another database?

Below is the code for triggering the tables in same database.

database_1 ---> schema_1 ---> table_1

|col1  col2|
_____|_____
|1a   1b   |
|2a   2b   |
|3a   3b   |
|4a   4b   |
|5a   5b   |

database_2 ---> schema_2 ---> table_2

|col1  col2|
_____|_____
|1a   1b   |
|2a   2b   |
|3a   3b   |


CREATE OR REPLACE TRIGGER "SCHEMA_1"."TRG_table_1_AFTER_UPDATE" 
    AFTER UPDATE ON "SCHEMA_1"."table_1"
    REFERENCING NEW AS new_row
    FOR EACH ROW
    NOT SECURED


Insert into SCHEMA_2.TABLE_2(col1, col2, col3) 
VALUES (new_row.val1, new_row.val2, new_row.val3);
END

CodePudding user response：

No way to do it with triggers.

The way to update tables in another database is use of nicknames.
But CREATE TRIGGER statement states:

SQL-procedure-statement
Specifies the SQL statement that is to be part of the triggered action. A searched update, searched delete, insert, or merge operation on nicknames inside compound SQL is not supported.

and

A procedure that contains a reference to a nickname in a searched UPDATE statement, a searched DELETE statement, or an INSERT statement is not supported (SQLSTATE 25000).

You may use some procedural logic with, say, 2PC-enabled federated servers, but not triggers.
Enabling two-phase commit for federated transactions