Posted by ~john on 07/14/06 17:56

I'm trying to get a transaction to work with PHP 5.1.4 and SQL Server
2005... The first SQL statement deletes several rows in the database.
The next SQL statement loops through and inserts several rows into the
same table. What I'm wanting is for the database to rollback all
transactions if any 1 of the inserts fail. What's happening now is the
DELETE statement runs successfully deleting several rows... but as soon
as one of the INSERTS fail, the page errors out WITHOUT rolling back
the DELETE statements. What am I doing wrong in my code?

thanks,

~john



mssql_query("BEGIN TRAN");

$sql = "DELETE FROM MyTable WHERE Value > 0";

$result = mssql_query($sql);

foreach(loopVar as val)
{
$sql = "INSERT INTO MyTable VALUES(1)";
$result = mssql_query($sql);

if( ! $result ){
mssql_query('ROLLBACK TRAN');
exit;
}

}

mssql_query("COMMIT TRAN");

[Back to original message]