CREATE TRIGGER sync_child_with_parent_update
BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
UPDATE child
SET
field_name_a = NEW.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, stephan nies <[EMAIL PROTECTED]> wrote:
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: sqlite-users@sqlite.org
> > 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: ' sqlite-users@sqlite.org'
> > > 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]
>
> -----------------------------------------------------------------------------
>
>
