Update second table based on values being saved in first

Hey all,

Maybe the title is kinda cryptic ....

But, what I want to do is this:

I am saving a record into table1. Based on this, I will need to save some values into table2. On inserting another record within table1, I want to update and edit (actually modify) the mentioned record in table2.

How do I do this. I am confusing myself into a corner here. Please help?

fats
ffelaarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ffelaarConnect With a Mentor Author Commented:
The solution I went with is as follows:

___________________________________

with qryDayAverages do
begin
 Close;
 Params.ParamByName('company').AsInteger := gCompanyID;
:
:
:
  Open;
end;

effortHours := VAL_Duration.AsInteger div 60;
effortMinutes := VAL_Duration.AsInteger mod 60;

with tblDay do
begin
  Open;
   Locate('TripCoIDFKey; TripVesselIDFKey; TripStartDate; DayDate',
            VarArrayOf([gCompanyID, gVesselID, gTripStartDate, gDragDate]),
            [loPartialKey]);
    Edit;
    if VAL_DragCount.AsInteger <= 1 then
    begin
      FieldByName('DayEffortHours').AsInteger := effortHours ;
      FieldByName('DayEffortMinutes').AsInteger := effortMinutes;
:
:
:
    tblDay.Post;
  end
  else
  begin
    FieldByName('DayAverageSpeed').AsFloat := VAL_AvgSpeed.Value;
:
:
:
      tblDay.Post;
    end;
  end;
0
 
ShadowFaxCommented:
What DB are you using?
0
 
LischkeCommented:
Hi Fats,

actually, this should not be a problem, although it depends obviously on the way you want to react. TTable has got an OnAfterInsert event. You could write an event handler for this event and modify your second table there. If you want to make it entirly database driven then you need a trigger like:

create trigger test for YourTable active after insert position 0

as
begin
  insert into Table2 set ID = New.ID
end

This trigger takes the newly inserted ID (suppose there would be one) and inserts a record with that ID into the second table (also provided there is an ID column).

Ciao, Mike
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ffelaarAuthor Commented:
Hey everyone,

Thanx for the quick response. I am using an Access 97 database.

Did you notice 200 points ;-)
0
 
kretzschmarCommented:
hi ffelaar,

use the beforepost-event from table1 for the update on table2, you can use for this a second table or a query.

explain a bit more

meikl
0
 
EpsylonCommented:
You can use TdataSet.Locate('someField', somevalue, []) to locate the record in Table2 and then edit the record in the AfterInsert event:

procedure Table1AfterInsert(...);
begin
with Table2 do
begin
  if Locate('someindexField', somevalue, []) then
    Edit
  else
    Insert;
  FieldByName('fieldtomodify').AsInteger := somevalue;
  Post;
end;
end;


Regards,

Epsylon.

0
 
mullet_attackCommented:
Hi Fats,

One way to do it...

1. add the record to the first table
2. add record to second table - make sure this table has AutoIncrement Primary key, and no other indices or filters
3. add another record to first table
4. 'last' on second table will return you the record added in step 2. You can now edit it.

This assumes you do this in a proc of some sort. If you want atomatic actions, you can use AfterPost event of table1 to check for the record in table2, add it if not there, or else edit it.

Mark
0
 
ffelaarAuthor Commented:
Not quite guys ....

Okay, I have two tables. Table1 and Table2 say.

On showing a particular form, I position myself onto the last record of table1.
I copy it and append it as a new record - ready for editing.

On saving of this record ... there will (trust me) be a record in Table2 to
be updated with some calculated values from table1.

If I use your advice of using AfterInsert on Table1 ... then I will run into a
problem ... because on making a duplicate of the last record as stated above ... it
will get called before I really have something to update it with. The record must
still be edited by the user ....

Just thought I'd make the issue clearer ....

Thanx so far
Fats
0
 
kretzschmarCommented:
hi ffelaar,

as i said, use the beforepost-event
and check if your table1 is in insert mode like

if table1.state = dsinsert then
//do your update stuff

meikl
0
 
FelixinCommented:
Here goes an example on an AFTERPOST event to update a second table when one is modified:

procedure TDM.AfterPost(DataSet: TDataSet);

  function Existe (Tabla : TTable) : Boolean;
  begin
    with QSelect do
    try
      Close;
      SQL.Clear;
      SQL.Add ('SELECT Count (*) AS Cuenta FROM Actualizaciones');
      SQL.Add ('WHERE UPPER (Tabla)=' + QuotedStr (UpperCase (Tabla.TableName)));
      Open;
      Result := (FieldValues ['Cuenta'] >= 1);
      Close;
    except
      raise;
    end;
  end;

  procedure Actualiza (Tabla : TTable);
  begin
    with QUpdate do
    begin
      try
        Close;
        SQL.Clear;
        SQL.Add ('UPDATE Actualizaciones');
        SQL.Add ('SET Tabla=' + QuotedStr (Tabla.TableName) + ',');
        SQL.Add ('    Hora='  + QuotedStr (FormatDateTime('mm/dd/yyyy h:nn:ss', Now)));
        SQL.Add ('WHERE UPPER (Tabla)=' + QuotedStr (UpperCase (Tabla.TableName)));
        ExecSql;
      except
        raise;
      end;
    end;
  end;

  procedure Inserta (Tabla : TTable);
  begin
    with QInsert do
    begin
      try
        Close;
        SQL.Clear;
        SQL.Add ('INSERT Actualizaciones (Tabla, Hora) VALUES');
        SQL.Add ('  (' + QuotedStr (Tabla.TableName) + ',');
        SQL.Add ('   ' + QuotedStr (FormatDateTime('mm/dd/yyyy h:nn:ss', Now)) + ')');
        ExecSql;
      except
        raise;
      end;
    end;
  end;

begin
  try
    DBAux.StartTransaction;
    if Existe (TTable (DataSet)) then // FIRST, I look for one record to be updated on the second table with the corresponding key
      Actualiza (TTable (DataSet)) // Record already exists then UPDATE

    else
      Inserta (TTable (DataSet)); // Record does not exist then INSERT
    DBAux.Commit;
  except
    DBAux.Rollback;
    raise;
  end;
end;
0
 
EpsylonCommented:
This goes wrong when Locate can't locate the right record. So I suggest to add an if statement:

If Locate(....) then
begin
  <your stuff>
end;
0
 
darinwCommented:
Hello all,

ffelaar has requested this question be deleted. Rather than lose the work done, I am saving the question to the PAQ.

darinw
Customer Service
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.