0

Actually I am think of a scenario like this:
Let's say we are managing a library which stores many books.
Our web application for the library allows user to add books to shelf.
The database has two tables, Books and Authors. Schemas are like these:

CREATE TABLE Books
(book_id int NOT NULL IDENTITY(1,1),
book_name nvarchar(100) NOT NULL,
author_id int NOT NULL,
PRIMARY KEY (book_id),
FOREIGN KEY ( author_id ) REFERENCES Authors(author_id),)

CREATE TABLE Authors
(author_id int NOT NULL IDENTITY(1,1),
author_name nvarchar(100) NOT NULL,
PRIMARY KEY (author_id))

Assume the request will get author name and book name to store the book on shelf.
It will automatically generate entry for author if there is no such author. But I want this operation to be a transaction.(I want everything rollback if something goes wrong.)
Can I get primary key before ending the transaction like this?

$server_name = "s3";
$connection_info = array( "Database"=>"bis_testing", "UID"=>"bisuser", "PWD"=>"111");
$conn = sqlsrv_connect( $server_name, $connection_info);

sqlsrv_begin_transaction( $conn )

$sql = "INSERT INTO Author(author_name)
       values
       (?);";

$author_name = 'Dr. Pro';
$stmt1 = sqlsrv_query( $conn, $sql, array($brand_name));
**// get author primary key to $author_pk**

$sql = "INSERT INTO Book(book_name, author_id)
       values
       (?,?);";

$book_name = 'Writing that works';
$stmt2 = sqlsrv_query( $conn, $sql, array($book_name, $author_pk));

if ($stmt1 && $stmt2) {
    echo 'done';
}

If not, how should I do the job?

Phil
  • 157,677
  • 23
  • 242
  • 245
Cato Yeung
  • 701
  • 2
  • 9
  • 17
  • Removed the MySQL tag. There is no MySQL in this question – Phil Apr 05 '13 at 04:43
  • I'm not a SQL-Server person, but try something like this? http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert – Sam Apr 05 '13 at 05:07

1 Answers1

0

I don't see a way to get the last insert id like in MySQL, but you can get the last inserted id from SQL Server as discussed here: Best way to get identity of inserted row?

You could also look into a stored procedure that would do what you need to do, although I don't know how you would call it with these functions.

Community
  • 1
  • 1
Revent
  • 2,091
  • 2
  • 18
  • 33
  • But that method do not apply when using transaction. I tried. And as a programmer, I think stored procedure will produce code that is hard to be maintain. Actually, another good option would be using GUID as primary key because I will know the key on the fly. – Cato Yeung Apr 05 '13 at 05:00