How does one insert records simultaneously into two database tables?

Question

I'm a newbie to databases and db's in Delphi, learning about both via online instructional materials. I'm struggling with a difference between real-life and the examples I'm finding. To be concrete, consider the common Books and Authors many-to-many relationship. Assume you've got a Book table (book_id, book_title, etc.), an Author table (author_id, author_name, etc.), and an AuthorBook join table. All three tables would have unique ID's, auto-generated, as primary keys.

The examples always begin with Author and Book information already inserted in their respective tables. In real-life, though, I think you'd be trying to insert records into both tables simultaneously, i.e., users would see a form or grid with places to enter a book's title and its author(s). How would something like that be coded in Delphi, assuming data-aware controls, an underlying Access database (or something else alterable via SQL)?

Answer 1

If you started with tables like these . . .

create table books (
  book_id integer primary key,
  book_title varchar(15) not null
);

create table authors (
  author_id integer primary key,
  author_name varchar(15) not null
);

create table book_authors (
  book_id integer not null references books (book_id),
  author_id integer not null references authors (author_id),
  primary key (book_id, author_id)
);

. . . and if you need to insert a new book and a new author at the same time, you might execute a SQL transaction like this.

begin transaction;
insert into books values (1, 'First book');
insert into authors values (1, 'First author');
insert into book_authors (book_id, author_id) values (1, 1);
commit;

Using a single transaction guarantees that either all three inserts are written to the database, or that none of them are. Alternatives are

• to build an updatable view in the database, joining all three tables, and inserting into the view,
• to write a stored procedure in the database, and insert through the stored procedure, and
• to insert into each table separately, which assumes that the existence of the book is important even if you don't know the author, and vice versa. (This is probably what I'd do for books and authors.)

If you were adding a new book for an existing author, you'd execute a slightly different transaction.

begin transaction;
insert into books values (2, 'Second book');
insert into book_authors (book_id, author_id) values (2, 1);
commit;

I imagine Delphi is like any other client-side language here. Instead of literal integers, you'd reference some property of the data-aware controls, perhaps a "value" or "text" property. And you'd execute the transaction in a button's "click" event.

If Delphi is sufficiently "data aware"--using controls that are bound to columns and rows in a database, like Access's native controls are--you might not need to execute any SQL or do anything special to save any automatic ID number the dbms generates; it will be accessible through one of the control's properties. (Access's forms and controls are highly data aware; that's how they work.) But if you have to, and you're using Microsoft's OLEDB provider for Access, you can use select @@identity to get the last id number used through your connection.

Answer 2

If using SQL, do something like this (pseudocode)

startTransaction;
INSERT INTO Book VALUES('Book1');
bookID:=SELECT LastAutoInc FROM #Dummy;
INSERT INTO Author VALUES('Author1');
authorID:=SELECT LastAutoInc FROM #Dummy;
INSERT INTO BookAuthor VALUES(bookID, autherID);
commit;

The key is the use of the LastAutoInc function (or the equivalent in your database) inside a transaction.