Hello, i'm working with DB2 9.1 over AIX and have an instance called db2inst1. In this instance have two database created (MERCA and TRAMITES). I need to update some tables of the TRAMITES database if a table in MERCA database receive an insert. I'm trying to do this with a trigger in the table of MERCA database, I federate the databases and create the nicknames objects from TRAMITES database in the MERCA database.

When I create the trigger the UPDATE event inside the trigger don't work and DB2 said that this is not a valid SQL call in the trigger.

Now I'm trying to implement a trigger in the table of the MERCA database in order to call a Stored Procedure (Java) that connects to the TRAMITES database and update the table that fires the triggers in the TRAMITES database.

Is this correct??? there is another way to do that I want to realize???

I will apreciate all the help and guide that you can give me....

Best regards

You should be at least able to create a SQL stored procedure to do what you want through the federation and then have the trigger call the stored procedure.

Andy

I try to do that you recomend and the SP works fine but when I call the SP from the trigger DB2 said the following:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"MERCA.ACTUALIZA_". Information returned for the error includes SQLCODE
"-20136", SQLSTATE "55047" and message tokens
"MERCA.MOD_REGISTRO_FEDERADO|MOD_REGISTRO". SQLSTATE=09000

The trigger in the after insert in the table is like the follows:

CREATE TRIGGER merca.actualiza_tabla AFTER INSERT ON merca.jti001 REFERENCING NEW AS NEWREC FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE busca INTEGER;
SET busca = (SELECT tomo FROM merca.fed_tramites_activos WHERE tomo = NEWREC.tomo AND asiento = NEWREC.asiento);

IF busca = NEWREC.tomo THEN
CALL merca.mod_registro_federado (NEWREC.tomo, NEWREC.asiento);
END IF;

END;


I accept sugestions....

Best Regards....

Have a look at the CREATE TRIGGER statement:
(http://publib.boulder.ibm.com/infoce...c/r0000931.htm)

We don't know how your procedure is defined by I guess this restriction on federated procedures applies to your latest error:
(http://publib.boulder.ibm.com/infoce...iyfafsp09.html)