Solved

Update second table based on values being saved in first

Posted on 2000-04-04
12
161 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
Comment Utility
What DB are you using?
0
 
LVL 10

Expert Comment

by:Lischke
Comment Utility
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
Comment Utility
Hey everyone,

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

Did you notice 200 points ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:ffelaar
Comment Utility
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
Comment Utility
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
Comment Utility
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 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now