title: PHP: Inserting using sqlsrv pdo using triggers
body: <p>I'm using sqlsrv pdo driver (used both 5.2 and 5.3 versions) and I'm seeing what I believe is unintended behavior.  Hopefully I'm doing something wrong.</p>
  
  <p>I've created 2 tables in sql express 2008.  One (<strong>Locations</strong>) is a table of primary keys shared with other tables.  The other table (<strong>Rooms</strong>) has 2 (or more) columns...a key column linked as a foreign key to the first table and one (or more) data columns.  So it looks like:</p>
  
  <p><strong>Locations</strong>  </p>
  
  <blockquote>
    <p>LocationID int<br>
    1<br>
    2<br>
    3<br>
    4<br>
    5  </p>
  </blockquote>
  
  <p><strong>Rooms</strong>  </p>
  
  <blockquote>
    <p>LocationID int, Roomname varchar(50)<br>
    2, 'living'<br>
    4, 'dining'</p>
  </blockquote>
  
  <p>2,4 both link back to the Locations table.</p>
  
  <p>Then I create a unique constraint on the Roomname column:</p>
  
  <pre><code>ALTER TABLE Rooms ADD CONSTRAINT UniqueRoom UNIQUE (Roomname)
  </code></pre>
  
  <p>Next, I created an INSTEAD OF INSERT trigger on the <strong>Rooms</strong> table:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[Rooms_INSERT] 
  on [dbo].Rooms 
  INSTEAD OF INSERT 
  as 
  BEGIN 
   INSERT INTO dbo.Locations DEFAULT VALUES 
   INSERT INTO dbo.Rooms select @@IDENTITY, Roomname from inserted    
  END  
  </code></pre>
  
  <p>Now I can insert data into the rooms table by:</p>
  
  <pre><code>INSERT INTO Rooms VALUES (null, 'roomname')
  </code></pre>
  
  <p>I use the sqlsrv PDO object to insert data through PHP:</p>
  
  <pre><code>$db = new PDO("sqlsrv:server=$serverName;Database=db", "", "");
  
  $sql=("insert into Rooms values (null,?)");
  $dbobj = $db-&gt;prepare($sql);
  if($dbobj-&gt;execute(array("dining")))
      print "&lt;p&gt;insert successful&lt;/p&gt;";
  else
      print "&lt;p&gt;insert unsuccessful&lt;/p&gt;";
  </code></pre>
  
  <p>Starting with the table listing above, I get the following results:</p>
  
  <ul>
  <li>Modify insert statement to "insert into Rooms values (?)" - fails as expected because it expects two args, "insert unsuccessful."   </li>
  <li>Revert insert statement to normal, attempt to insert array("bedroom") - successful as expected, "insert successful."</li>
  <li>Re-attempt insert of array("bedroom") - db insert fails, no entries are made in either table, but attempt is reported successful "insert successful."</li>
  </ul>
  
  <p>Why is the 3rd attempt reported as successful when there were no lasting entries made to the database? Or, more specifically, how can I modify either my PHP or my SQL to properly detect when an insert like this fails due to a violation of a unique constraint?</p>