title: Values() constructor in SQL Server 2008 and triggers
body: <p>I have two tables <code>Employee</code> and <code>Emp_Audit</code>.</p>
  
  <p>On table <code>Employee</code>, I have an <code>AFTER INSERT</code> trigger, which fires when I insert rows into <code>Employee</code>. The function of trigger is to insert the rows into the <code>Emp_Audit</code> table that have been inserted into <code>Employee</code>.</p>
  
  <p>The trigger works fine when I explicitly use 'insert values' for each record to be inserted in <code>Employee</code> as </p>
  
  <pre><code> INSERT INTO Employee_Test VALUES ('Anees',1000);
   INSERT INTO Employee_Test VALUES ('Rick',1200);
   INSERT INTO Employee_Test VALUES ('John',1100);
   INSERT INTO Employee_Test VALUES ('Stephen',1300);
   INSERT INTO Employee_Test VALUES ('Maria',1400);
  </code></pre>
  
  <p>Trigger inserts all these rows inti  <code>Emp_Audit</code> -------------GOOD</p>
  
  <p>But when I use values constructor as</p>
  
  <pre><code>insert into dbo.Employee_Test
  values   ('Kritika', 25000),
           ('Ritu', 15000),
           ('Maduri', 7000),
           ('Dinkar', 7000);
  </code></pre>
  
  <p>Only the first row <code>('Kritika', 25000)</code> gets inserted into <code>Emp_Audit</code></p>
  
  <p>The whole query is as follows:</p>
  
  <pre><code>CREATE TABLE Employee_Test
  (
  Emp_ID INT Identity,
  Emp_name Varchar(100),
  Emp_Sal Decimal (10,2)
  )
  
  CREATE TABLE Employee_Test_Audit
  (
       Emp_ID int,
       Emp_name varchar(100), 
       Emp_Sal decimal (10,2), 
       Audit_Action varchar(100), 
       Audit_Timestamp datetime 
  ) 
  
  -----------------------Trigger------------------------------------
  CREATE TRIGGER trgInsertAfter ON [dbo].[Employee_Test]
  FOR INSERT 
  AS
     declare @empid int; 
     declare @empname varchar(100); 
     declare @empsal decimal(10,2); 
     declare @audit_action nvarchar(200); 
  
     select @empid = inserted.Emp_ID   
     FROM inserted;  
     select @empname = inserted.Emp_name 
     from inserted;  
     select @empsal = inserted.Emp_Sal  
     from inserted;  
  
     set @audit_action = 'Record Inserted after Insert Trigger Fired';  
  
     INSERT INTO Employee_Test_Audit 
     VALUES(@empid, @empname, @empsal, @audit_action, GETDATE());  
     GO
  
     print('Insert trigger FIRED') 
  
     insert into dbo.Employee_Test  
     values ('Kritika', 25000), 
            ('Ritu', 15000), 
            ('Maduri', 7000), 
            ('Dinkar', 7000); 
  </code></pre>