Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
Calling procedure between 2 Databases |
Fri, Feb 27 2009 6:39 AM | Permanent Link |
"Hershcu S" | 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 |
Fri, Feb 27 2009 8:16 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Feb 27 2009 9:34 AM | Permanent Link |
Leslie | 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 |
Fri, Feb 27 2009 10:18 AM | Permanent Link |
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 |
Fri, Feb 27 2009 11:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Leslie
Can you post the code? Roy Lambert |
Fri, Feb 27 2009 12:09 PM | Permanent Link |
"Hershcu S" | > If you want more information on this, just let me know.
Yes if it's not bother Thanks Sorin |
Fri, Feb 27 2009 12:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Feb 27 2009 12:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Fri, Feb 27 2009 12:44 PM | Permanent Link |
"Hershcu S" | 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 > |
Fri, Feb 27 2009 1:55 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |