title: SQL Server: Inserting into table with INSTEAD OF INSERT triggers
body: <p>This <a href="https://docs.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables" rel="nofollow noreferrer">Microsoft Documentation</a> states that the "rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger".</p>
  
  <p>However:</p>
  
  <pre><code>CREATE TABLE tbTriggerTest (id INT PRIMARY KEY);
  GO
  
  CREATE TRIGGER dbo.tgTriggerTest
  ON dbo.tbTriggerTest
  INSTEAD OF INSERT
  BEGIN
      SELECT * FROM INSERTED
  END
  GO
  
  INSERT tbTriggerTest DEFAULT VALUES
  GO
  </code></pre>
  
  <p>does not throw an error even though at the INSERT statement level I'm inserting a null value into the primary key. In fact INSERTED.id is NULL in this case. So is the documentation incorrect OR does it only apply inside the trigger?</p>
  
  <p><strong>EDIT:</strong> Responding to a proposed answer stating that caller must supply some value for a non null column, even if it is null, the following does not throw an error yet arguably does not supply any value for column <code>ij</code>. </p>
  
  <pre><code>CREATE TABLE tbTriggerTest2
  (
      id INT,
      ij INT,
      CONSTRAINT PK PRIMARY KEY (id, ij)
  )
  GO
  
  CREATE TRIGGER dbo.tgTriggerTest2
     ON  dbo.tbTriggerTest2
     INSTEAD OF INSERT
  AS 
  BEGIN
      SELECT * FROM INSERTED;
  END
  GO
  
  INSERT tbTriggerTest2 (id) VALUES (0)
  GO
  </code></pre>