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.

asked 15 Apr '11, 09:13

juampii's gravatar image

juampii
16112
accept rate: 0%


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;
permanent link

answered 15 Apr '11, 10:13

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.4k33579
accept rate: 39%

edited 15 Apr '11, 10:15

3

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.

(15 Apr '11, 10:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61

question asked: 15 Apr '11, 09:13

question was seen: 770 times

last updated: 15 Apr '11, 10:19