Trigger to enforce uniqueness across databases

Is it possible to create an insert or update trigger that checks the value of a particular field to be stored, looks up in a table on another database to check that that value does not exist there, and returns an error and prevents the update/insert if it does? For example: in database1 we are checking the value of product_no to be inserted into the product table (product_no is a unique key). But we also now need ot check that the value of product_id would not already be in database2 either, so our trigger must check for the existance of product_no in the product table on database2 and, if it exists, prevent the update/insert and barf. thanks Neil

Yes. You can create an insert/update trigger which invokes a stored procedure and then the stored procedure does a remote query. Then if the rules of the SP are not met, then you can raise an error condition. However, what are you going to do if the network is down/flaky? M.P. "Neil Truby" <neil.truby@ardenta.com> wrote in message news:2uaif9F23e9djU1@uni-berlin.de... > Is it possible to create an insert or update trigger that checks the value > of a particular field to be stored, looks up in a table on another database > to check that that value does not exist there, and returns an error and > prevents the update/insert if it does? > > For example: in database1 we are checking the value of product_no to be > inserted into the product table (product_no is a unique key). But we also > now need ot check that the value of product_id would not already be in > database2 either, so our trigger must check for the existance of product_no > in the product table on database2 and, if it exists, prevent the > update/insert and barf. > > thanks > Neil > >

"Madison Pruet" <mpruet@comcast.net> wrote in message news:5Z7gd.321531$MQ5.234285@attbi_s52... > Yes. > > You can create an insert/update trigger which invokes a stored procedure > and > then the stored procedure does a remote query. Then if the rules of the > SP > are not met, then you can raise an error condition. > > However, what are you going to do if the network is down/flaky? Both databases are in the same database server.

Neil Truby wrote: > "Madison Pruet" <mpruet@comcast.net> wrot: >>Neil Truby asked: >>> Is it possible to create an insert or update trigger that >>> checks the value of a particular field to be stored, looks up >>> in a table on another database to check that that value does >>> not exist there, and returns an error and prevents the >>> update/insert if it does? >>> >>> For example: in database1 we are checking the value of >>> product_no to be inserted into the product table (product_no is >>> a unique key). But we also now need ot check that the value of >>> product_id would not already be in database2 either, so our >>> trigger must check for the existance of product_no in the >>> product table on database2 and, if it exists, prevent the >>> update/insert and barf >> >>Yes. >> >> You can create an insert/update trigger which invokes a stored >> procedure and then the stored procedure does a remote query. Then >> if the rules of the SP are not met, then you can raise an error >> condition. >> >>However, what are you going to do if the network is down/flaky? > > Both databases are in the same database server. ER permits you to set up the serial columns in the two databases so that db1 would, for sake of argument, only generate odd numbers and db2 would only generate even numbers. The lookup is then unnecessary. (And I don't think you have to have ER in use to achieve this, though it is more normally used when the systems are coupled with ER.) You could achieve the same effect with disjoint sequences - a sequence in db1 set up to generate only odd numbers and another in db2 set up to generate only even numbers. Alternatively again, use a serial number in each database, but in db1, when the serial number generated is N, store 2*N+1, and in db2, when the serial number generated is N, store 2*N+0. If the set of numbers really must be dense and non-overlapping, you have problems if one database goes down and the other doesn't. Granted, in a single server, that's pretty unlikely -- though if dbspace1 containing db1 goes down while dbspace2 containing db2 remains functional, you could have problems - two sets of problems (recovering db1 and resyncing the numbers, if any need to be which they most likely wouldn't). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Another resolution to this sort of situation that I have used is to have a composite primary key, col1 char(5) default "siteX"/"siteY", col2 serial any subsequent jkoin columns require a 2 column join. Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<W3Fgd.6421$kM.3110@newsread3.news.pas.earthlink.net>... > Neil Truby wrote: > > "Madison Pruet" <mpruet@comcast.net> wrot: > >>Neil Truby asked: > >>> Is it possible to create an insert or update trigger that > >>> checks the value of a particular field to be stored, looks up > >>> in a table on another database to check that that value does > >>> not exist there, and returns an error and prevents the > >>> update/insert if it does? > >>> > >>> For example: in database1 we are checking the value of > >>> product_no to be inserted into the product table (product_no is > >>> a unique key). But we also now need ot check that the value of > >>> product_id would not already be in database2 either, so our > >>> trigger must check for the existance of product_no in the > >>> product table on database2 and, if it exists, prevent the > >>> update/insert and barf > >> > >>Yes. > >> > >> You can create an insert/update trigger which invokes a stored > >> procedure and then the stored procedure does a remote query. Then > >> if the rules of the SP are not met, then you can raise an error > >> condition. > >> > >>However, what are you going to do if the network is down/flaky? > > > > Both databases are in the same database server. > > ER permits you to set up the serial columns in the two databases so > that db1 would, for sake of argument, only generate odd numbers and > db2 would only generate even numbers. The lookup is then unnecessary. > (And I don't think you have to have ER in use to achieve this, though > it is more normally used when the systems are coupled with ER.) > > You could achieve the same effect with disjoint sequences - a sequence > in db1 set up to generate only odd numbers and another in db2 set up > to generate only even numbers. > > Alternatively again, use a serial number in each database, but in db1, > when the serial number generated is N, store 2*N+1, and in db2, when > the serial number generated is N, store 2*N+0. > > If the set of numbers really must be dense and non-overlapping, you > have problems if one database goes down and the other doesn't. > Granted, in a single server, that's pretty unlikely -- though if > dbspace1 containing db1 goes down while dbspace2 containing db2 > remains functional, you could have problems - two sets of problems > (recovering db1 and resyncing the numbers, if any need to be which > they most likely wouldn't).