 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / December 2004Linked Oracle db in Access 2000
I created a db in Oracle and then linked the tables in Access 2000. I'm having a problem with the triggers that I created in Oracle. Only one of my triggers works in Access eventhough they are enabled and valid in Oracle. The triggers that aren't working are included in some mapping tables that I created to support one-many relationships amongst various tables. Since my triggers don't work, the mapping tables, and consequently the forms that use them aren't working properly. Is there an easy fix to get the triggers working in Access? What do the triggers do? Populate a column with the next value from a sequence when each row is inserted? How are they "not working"? Is it that you can't "see" the value (or you "see" the incorrect value) in the column that the trigger populates after you insert a row? Triggers in Oracle should "work" (that is, execute) regardless of how you access the data, be it through Access, SQL*Plus, Java, or whatever. > I created a db in Oracle and then linked the tables in Access 2000. I'm > having a problem with the triggers that I created in Oracle. Only one of my [quoted text clipped - 5 lines] > > Is there an easy fix to get the triggers working in Access? Yes, that's what they are supposed to do. I have tables set up like this: I have a table that is used to collect information on a particular patient (primary key is an id number generated by the aforementioned trigger), a table that holds diagnosis information on each patient (primary key is also a trigger-generated id number), and a table that maps these two tables together (has the trigger-generated id number (PK) and includes the PK's from the other two tables as FK's) . The triggers for the patient and diagnosis primary keys work fine. The mapping table trigger for it's primary key doesn't work. Since there is no primary key being generated in the mapping table (at least I think that's the reason), I can't get a form that includes the patient table as the main form and the diagnosis table as a subform to work correctly. I'm new to using Access with an Oracle db, so any help would be greatly appreciated! > What do the triggers do? Populate a column with the next value from a > sequence when each row is inserted? [quoted text clipped - 19 lines] > > > > Is there an easy fix to get the triggers working in Access? Why do you need the third table if the second table already "holds diagnosis information *on each patient*"? In any case, in my experience, to get Access to correctly handle linked Oracle tables that have columns populated by triggers, you must: 1. Create a primary key or unique constraint in Oracle on a column or set of columns whose value(s) are "known" when a row is inserted (that is, are not populated by a trigger). For example, in your case you might need a unique constraint on each table in addition to the primary keys on the trigger-populated columns. In the "diagnosis" table, this might be on a DIAGNOSIS_NAME column, if there is one. In the "mapping" table, this could be on the combination of the two FK columns. In the "patients" table, your choices might be less clear (for example, PATIENT_NAME is probably not unique). If you don't have a "candidate" key, you might in the BeforeInsert event of your form open a recordset on a linked view or pass-through query that gets the next sequence number, and then set the value of the bound PATIENT_ID column to the value returned. You would need to modify your trigger so that it does not populate the PATIENT_ID column if a value is provided. 2. Create a pseudo-index on the linked table *in Access* (check the help) on the column(s) from 1. When you link tables, Access sometimes creates pseudo-indexes automatically based on the constraints and indexes defined on the source table. Sometimes these are not the ones you want. To work around this, you have to drop the pseudo-indexes Access creates (or link to an Oracle-defined view of the base table, for which Access will not create pseudo-indexes), and then create the ones you want. Hope this helps. > Yes, that's what they are supposed to do. > [quoted text clipped - 37 lines] > > > > > > Is there an easy fix to get the triggers working in Access? I guess I don't really need the third table; I was building it that was at the suggestion of someone else to better handle the one-to-many relationship. I think that I'm going to eliminate the third table, which is causing me to do double work. I'll keep your ideas in mind, though, on the unique constraints if I decide to keep the third table. Thanks so much for your help! > Why do you need the third table if the second table already "holds diagnosis > information *on each patient*"? [quoted text clipped - 88 lines] > > > > > > > > Is there an easy fix to get the triggers working in Access? I expect you will still need the unique constraints on the other two tables. You typically need a "third" table to model *many*-to-many relationships. For example, if you have PATIENTS: PATIENT_ID, PATIENT_NAME,... 1, John,... 2, Bob,... 3, Mary,... . . . and CONDITIONS: CONDITION_ID, CONDITION_NAME,... 1, Measles,... 2, Mumps,... 3, Rubella,... 4, Chicken Pox,... . . . You might want to keep track of when patients were diagnosed with certain conditions (which in this case could happen more than once) using a table like this: DIAGNOSES: DIAGNOSIS_ID, PATIENT_ID, CONDITION_ID, DATE_DIAGNOSED,... 1, 2, 1, 1/1/2004,... 2, 2, 2, 2/1/2004,... 3, 2, 1, 10/1/2004,... 4, 3, 2, 10/14/2004,... . . . So, John was diagnosed with measles on January 1 and again on October 1. John was also diagnosed with mumps on February 1, and Mary was diagnosed with mumps on October 14. In this example, you might have a unique constraint on PATIENT_ID, CONDITION_ID, and DATE_DIAGNOSED. This allows many patients to be diagnosed with the same condition, the same patient to be diagnosed with many conditions, and the same patient to be diagnosed with the same condition more than once, but not on the same date. Sometimes, the combination of foreign keys in these kinds of "junction" tables is unique by itself. > I guess I don't really need the third table; I was building it that was at > the suggestion of someone else to better handle the one-to-many relationship. [quoted text clipped - 98 lines] > > > > > > > > > > Is there an easy fix to get the triggers working in Access? That's exactly what I need!!! Thanks! > I expect you will still need the unique constraints on the other two tables. > [quoted text clipped - 184 lines] > > > > > > > > > > > > Is there an easy fix to get the triggers working in Access? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.