Trigger to auto-generate a record in another table

  • Thread starter Thread starter Yasir Usman
  • Start date Start date
Y

Yasir Usman

Let's say I have two tables A and B.
Structure for tables:
A is id, A1, A2
B is id, B1, B2, Aid where Aid is foreign key that one
can relate table A to table B

I would like to construct a form that enables users to
perform data entry in the following manner:

1. User 1 will first create a record in table A by
entering column A1 only. A simple form will do this where
the only column in the form is A1. No problems yet.

2. At a later date, User 2 will need to fill in the
following data as it pertains to the entity that User 1
entered:
- any combination of A2, B1, B2

Ideally the form would take the following shape:
A1 - non-editable (for identification only)
A2 - editable
B1 - editable
B2 - editable

I have problems creating a usable form for user 2 because
Aid is a foreign key on table B i.e. it does not yet
exist. With such a form user 2 cannot add any new data
unless they had explicitly gone to table B to generate a
record in table B and tied it to table A via the foreign
key. That is too much to ask of user 2.


My question is:
1. Does anyone know how I can create a trigger when a
user enters data in table A that automatically generates
a corresponding record in table B with an id, Aid

Or

2. Does anyone know how I could create a script that
generates records in table B with an id, Aid when User 2
loads their data entry form

Or

3. Any other ideas?

BTW, I am to a certain degree constrained to the current
dB schema
 
I have something similar - where I want to update linked
data in multiple tables. I use an unbound form with a
ListBox and editable controls.

Using your example, the list box would display 4 fields
A1, A2, B1, B2 and there would be a TextBox for each one.
Whan you click on a row in the ListBox, the data is
populated into the appropriate TextBoxes. The user can
add or edit the data - or just enter it straight, if
adding a new record - and the a CommandButton adds/updates
it and refreshes the ListBox.

The flow of the code is:
If A1 is empty, end
Check if A1 already exists in table A. If not, add A1
Click to expand...
and A2 (even if null values). If so, prompt user whether
to overwrite/update.
Check if A1 already exists in table B. If not, add A1,
Click to expand...
B1, B2 (even if null values). If so, prompt user whether
to overwrite/update.
Refresh ListBox
Click to expand...

Hope this helps!

Howard Brody
 
Back
Top