Hello

On my app. I have 2 databases for one sesion
Can I use a trigger from a table on the first database to call a procedure
that delete a record on the second database

Thanks Sorin

Sorin,

<< On my app. I have 2 databases for one sesion Can I use a trigger from a
table on the first database to call a procedure that delete a record on the
second database >>

Unfortunately, no.  SQL/PSM trigger, procedures, and functions cannot access
tables outside of the current database.  You can, however, do this with an
external procedure.  If you want more information on this, just let me know.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Unfortunately, no.  SQL/PSM trigger, procedures, and functions cannot access
tables outside of the current database.  You can, however, do this with an
external procedure.  If you want more information on this, just let me know.>>

I am confused.  Till now I was certain that I had used triggers to syncronyze data between
tables in different databases in a small test app. Need to check what I have missed ...


Leslie

Tim,

Checked it again in the EDBManager. An after insert trigger inserted the new record into a
table located in an other database without any problem.

Leslie

Leslie


Can you post the code?

Roy Lambert

> If you want more information on this, just let me know.

Yes if it's not bother

Thanks Sorin

Leslie,

<< Checked it again in the EDBManager. An after insert trigger inserted the
new record into a table located in an other database without any problem. >>

Duh, I just realized that you're right - you can execute an SQL INSERT
statement against another database in a trigger, provided that you preface
the table names with the proper database name.  Using EXECUTE or EXECUTE
IMMEDIATE will do the trick.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sorin,

Sorry, I was mistaken.  You can access other databases in triggers - see
Leslie's responses.

--
Tim Young
Elevate Software
www.elevatesoft.com

Just a sintax example. How to proper preface the external database?

Sorin

> Duh, I just realized that you're right - you can execute an SQL INSERT
> statement against another database in a trigger, provided that you preface
> the table names with the proper database name.  Using EXECUTE or EXECUTE
> IMMEDIATE will do the trick.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Hershcu,

> Just a sintax example. How to proper preface the external database?

EXECUTE IMMEDIATE '
  INSERT INTO OtherDB.Clients(Id, Name)
     SELECT Id Name FROM ThisDB.Clients(Id, Name) ';


EXECUTE IMMEDIATE '
  INSERT INTO Clients(Id, Name)
     SELECT Id Name FROM "Other DB".Clients(Id, Name) ';

EXECUTE IMMEDIATE '
  INSERT INTO "Other DB".Clients(Id, Name)
     SELECT Id Name FROM Clients(Id, Name) ';


--
Fernando Dias
[Team Elevate]