sorry hit send by accident this should be:
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 = NEW.field_name_b,
field_name_c = NEW.field_name_c,
WHERE
field_name_a = NEW.field_name_a
END;
// assuming field_name_a is the id field
On 3/29/07, stephan nies <[EMAIL PROTECTED]> wrote:
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.ButI'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]
> >
-----------------------------------------------------------------------------
> >
> >
> >
>
