I think using triggers would be a good solution:
You good just trigger on inserts and updates to parent
and do whatever you like to child.
Example:
CREATE TRIGGER sync_child_with_parent_insert
BEFORE INSERT ON parent
FOR EACH ROW BEGIN
INSERT INTO child
(field_name_a,
field_name_b,
field_name_c)
VALUES
(NEW.field_name_a,
NEW.field_name_b,
NEW.field_name_c);
END;
CREATE TRIGGER sync_child_with_parent_update
BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
INSERT INTO child
(field_name_a,
field_name_b,
field_name_c)
VALUES
(NEW.field_name_a,
NEW.field_name_b,
NEW.field_name_c);
END;
On 3/29/07, P Kishor <[EMAIL PROTECTED]> wrote:
On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> Thanks for your reply.
>
> I'm writing my application in Perl using SQLite as file based database.
> Multiple users would be accessing the database simultaneously.But I'll
> lock the file prior to any updations to the database. I'm planning to
> use a third table to generate ids for the master table. But I don't know
> how to select id INTO a variable which can be used while inserting data
> into both the tables. Is it possible?
Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.
# get your values to insert somehow
my $field1 = "my field one";
my $name = "my name";
# enable transactions, if possible
$dbh->{AutoCommit => 0, RaiseError => 1};
eval {
my $sth_master = $dbh->prepare(qq{
INSERT INTO Master ( field1 ) VALUES ( ? )
});
$sth_master->execute( $field1 );
my $master_id = $dbh->func('last_insert_rowid');
my $sth_child = $dbh->prepare(qq{
INSERT INTO Child ( id, name ) VALUES ( ?, ? )
});
$sth_master->execute( $master_id, $name );
# commit the changes if we get this far
$dbh->commit;
};
if ($@) {
warn "Transaction aborted because $@";
# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh->rollback };
# add other application on-error-clean-up code here
}
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: 28 March 2007 17:42
> To: [email protected]
> Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
>
>
> On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > Can anyone please look into my query,
> >
> > I've tables
> >
> > Master ( id integer primary key,
> > field1 text)
> >
> > And
> >
> > Child (id integer, name text)
> >
> > My application receive values for field1 and name. I need to populate
> > master and child with incoming values using the same id.
> >
>
> 1. Insert "field1" in Master;
> 2. Get id of record inserted from #1 above;
> 3. Insert id from #2 and "name" in Child.
>
> That is about all the help I can provide given that you have not
> provide any further context... what language you are using, how you
> are developing the application, etc.
>
> SQLite does provide a 'last_insert_rowid' capability. Look on the
> sqlite.org website.
>
> if you are 100% sure that no one else will be fiddling with your
> database, you can probably use something like
>
> INSERT INTO Child( id, name)
> VALUES ( (SELECT Max(id) FROM Master), <name>)
>
>
> > A quick reply will be highly appreciated.
> >
> > Regards
> > Ajay
> >
> > -----Original Message-----
> > From: Arora, Ajay
> > Sent: 28 March 2007 15:04
> > To: '[email protected]'
> > Subject: Maintaining Master-Child relationships
> >
> >
> > Hi,
> >
> > I've two tables in my database, a master and a child with ID as a
> > common key.
> >
> > I've created a table with one column to generate the sequence
> number.How
> > can I insert related records into both the tables using same ID?
> >
> > Thanks
> > Ajay
> >
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
