Trigger between databases

[Former Member]

Hello.

I need to call a procedure of a database from other database with trigger. Can you send me a example script?. Thank you.

Gabriel.

[regdomaratzki]

First, define the schema of the database that will be called into :

create table two_table ( pk integer primary key, c1 integer );
create procedure two_procedure ( in @pk integer, in @c1 integer )
begin
  insert into two_table values ( @pk, @c1 );
end;

Next, create the schema of the database that will make the remote procedure call :

create table one_table ( pk integer primary key, c1 integer );

create server two class 'SAODBC' using 'driver=SQL Anywhere 12;eng=two;dbn=two;uid=dba;pwd=sql';
create externlogin DBA to two;
create procedure one_procedure ( in @pk integer, in @c1 integer ) at 'two..DBA.two_procedure';

create trigger one_trigger after insert on one_table
referencing new as nr for each row
begin
  call one_procedure ( nr.pk, nr.c1 );
end;

Now, an insert into the "one_table" table will fire a trigger that calls "one_procedure" which is a remote procedure call to the "two_procedure" stored procedure on the other database, which will insert the same values into the "two_table" table.

insert into one_table values ( 1,1 );
commit;

[VolkerBarth]

I'd like to add that thoughts on exception handling are very recommended here:

In case the second database might be unavailable, an insert in the first database will fail as the call to one_procedure() will fail. That will rollback the according transaction.

That may or may not be acceptable.