Thanks Kishore/Stephen for your suggestions. Regards Ajay
-----Original Message----- From: stephan nies [mailto:[EMAIL PROTECTED] Sent: 29 March 2007 15:54 To: [email protected] Subject: Re: [sqlite] RE: Maintaining Master-Child relationships 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: [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] > > > > ------------------------------------------------------------------------ ----- > > > > > THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
