[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update second table based on values being saved in first

Posted on 2000-04-04
12
Medium Priority
?
169 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:ffelaar
  • 3
  • 2
  • 2
  • +5
12 Comments
 

Expert Comment

by:ShadowFax
ID: 2683425
What DB are you using?
0
 
LVL 10

Expert Comment

by:Lischke
ID: 2683430
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
 

Author Comment

by:ffelaar
ID: 2683488
Hey everyone,

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

Did you notice 200 points ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 2683717
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
 
LVL 13

Expert Comment

by:Epsylon
ID: 2683735
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
 
LVL 2

Expert Comment

by:mullet_attack
ID: 2686218
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
 

Author Comment

by:ffelaar
ID: 2686507
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2686537
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
 
LVL 2

Expert Comment

by:Felixin
ID: 2689945
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
 

Accepted Solution

by:
ffelaar earned 800 total points
ID: 2696902
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
 
LVL 13

Expert Comment

by:Epsylon
ID: 2696913
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
 
LVL 3

Expert Comment

by:darinw
ID: 2697118
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question