How to insert records simultaneously into two database tables?

Question

I am new to Delphi databases and db, learning about this through online tutorials. I struggle with the difference between real life and the examples that I find. To be specific, consider the general relationship between books and many-to-many authors. Suppose you have a table of books (book_id, book_title, etc.), a table of authors (author_id, author_name, etc.) and a table for combining authors. All three tables will have unique identifiers that are automatically generated as primary keys.

Examples always begin with information about the author and the book already inserted in the corresponding tables. In real life, I think you are trying to insert records into both tables at the same time, that is, users will see a form or grid with places to enter the name of the book and its author (s). How would something like this be encoded in Delphi, assuming data-based controls, an Access base database (or something else that can be changed using SQL)?

Answer 1

If you started with such tables.,.

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 can execute an 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 ensures that either all three inserts are written to the database, or none of them. Alternatives

• to create an updatable view in the database, join all three tables and insert into the view
• to record the stored procedure in the database and insert through the stored procedure and
• to insert into each table separately, which suggests that having a book is important, even if you don’t know the author, and vice versa. (This is probably what I would do for books and authors.)

If you added a new book to an existing author, you would have performed 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 believe Delphi is like any other client language. Instead of literal integers, you should refer to some property of the data-driven controls, possibly the value or text property. And you completed the transaction in the click event.

If Delphi is sufficiently “data aware” —through controls bound to columns and rows in the database, such as its own access controls — you may not need to execute any SQL or do anything special to preserve any automatic identifier is the number generated by dbms; it will be available through one of the management properties. (Access forms and controls are knowledgeable about the data and how it works.) But if you need and you use the Microsoft OLEDB provider for access, you can use select @@identity to get the last identifier number used in your connection.

Answer 2

If you are using SQL, do something like this (pseudo code)

 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 to use the LastAutoInc function (or the equivalent in your database) inside the transaction.