The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
4.8k33269
accept rate: 41%

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:

×58

question asked: 15 Apr '11, 09:13

question was seen: 714 times

last updated: 15 Apr '11, 10:19