title: IF statement in SQL Server trigger with condition
body: <p>I created a table <code>student</code> and I want to create a trigger to stop adding
  teacher id after 6 to any student so every 5 student have only 1 teacher.</p>
  
  <p>Teacher id in <code>student</code> table is a foreign key so I can repeat in student table
  5 times and when I insert 6.. don't agree  I try this code but it does not work</p>
  
  <p>The <code>student</code> table looks like this:</p>
  
  <pre><code>CREATE TABLE student
  (
      s_id int PRIMARY key,
      s_name varchar(50) ,
      birthday date ,
      t_id int 
  )
  </code></pre>
  
  <p>And the trigger:</p>
  
  <pre><code>CREATE TRIGGER tr_student 
  on student
  DECLARE
      @count int,
      @s_id int,
      @s_name nvarchar(50),
      @birthdate date,
      @t_id int
  Begin
      select @s_id = i.s_id from inserted i;
      select @s_name = i.s_name from inserted i;
      select @birthdate = i.birthday  from inserted i;
      select @t_id = i.t_id from inserted i;
  
      Select count(*) t_id
      From student
      Where @count = t_id
  
      If @count &lt; 6 
         Insert into student(s_id, s_name, birthday, t_id)
         values(@s_id, @s_name, @birthdate, @count)
  
      PRINT 'AFTER INSERT trigger fired'
  End  
  </code></pre>