title: Triggers Creation on Insert on a View in SQL Server 2019
body: <p>I have a table:</p>
  <pre><code>CREATE TABLE dbo.employee_assignment 
  (
      employee_id BIGINT,
      department_id BIGINT,
      job_title_id BIGINT,
      manager_id BIGINT,
      effective_start_date SMALLDATETIME,
      effective_end_date SMALLDATETIME,
      action_code VARCHAR(60)
  );
  </code></pre>
  <p>I populated it with values:</p>
  <pre><code>INSERT INTO dbo.employee_assignment (employee_id, department_id, job_title_id, manager_id, effective_start_date, effective_end_date, action_code) 
  VALUES
       (1,2,8,3,'2018-06-08 00:00:00',NULL,'MANAGER_CHANGE'),
       (2,2,3,6,'2012-08-10 00:00:00','2013-08-10 00:00:00','MANAGER_CHANGE'),
       (3,5,6,3,'2014-07-29 00:00:00',NULL,'MANAGER_CHANGE'),
       (4,1,10,6,'2011-02-03 00:00:00','2015-06-04 00:00:00','HIRE'),
       (4,1,11,6,'2015-06-04 00:00:00',NULL,'GRADE_CHANGE'),
       (2,2,3,8,'2012-08-10 00:00:00','2013-08-10 00:00:00','GRADE_CHANGE'),
       (7,7,1,6,'2021-06-06 00:00:00',NULL,'MANAGER_CHANGE'),
       (1,2,8,8,'2016-06-07 00:00:00','2017-06-07 00:00:00','HIRE'),
       (8,5,7,12,'2018-04-18 00:00:00',NULL,'HIRE'),
       (3,5,6,12,'2013-10-25 22:12:01','2012-08-10 00:00:00','HIRE');
  </code></pre>
  <p>Then I created a view of it:</p>
  <pre><code>CREATE VIEW v_employee_assignment 
  AS
      SELECT 
          employee_id, department_id, job_title_id, manager_id, 
          effective_start_date, effective_end_date, action_code
      FROM 
          employee_assignment
  </code></pre>
  <p>and two more tables with a copy of <code>employee_assignment</code></p>
  <pre><code>SELECT * 
  INTO employee_assignment_history
  FROM employee_assignment;
  
  SELECT * 
  INTO employee_assignment_future
  FROM employee_assignment;
  </code></pre>
  <p>I'm trying to create a trigger that each time a new record is inserted into employee_assignment view, check the following:</p>
  <ul>
  <li>If effective_start_date &gt; current_date, then it should be inserted only into <code>employee_assignment_future</code>;</li>
  <li>If effective_start_date &lt;= current_date, then it should insert it both into <code>employee_assignment</code> and <code>employee_assignment_history</code></li>
  </ul>
  <p>Code:</p>
  <pre><code>CREATE OR ALTER TRIGGER t_v_employee_assignment 
  ON v_employee_assignment
  INSTEAD OF INSERT
  AS
          DECLARE @employee_id BIGINT
          DECLARE @department_id BIGINT
          DECLARE @job_title_id BIGINT
          DECLARE @manager_id BIGINT
          DECLARE @effective_end_date SMALLDATETIME
          DECLARE @action_code VARCHAR(60)
          DECLARE @effective_start_date SMALLDATETIME
      BEGIN
              IF  @effective_start_date &gt;  GETDATE()
              BEGIN
                  INSERT INTO employee_assignment_future (employee_id,
                                                          department_id,
                                                          job_title_id,
                                                          manager_id,
                                                          effective_start_date,
                                                          effective_end_date ,
                                                          action_code)
                                                          VALUES (@employee_id,
                                                                  @department_id,
                                                                  @job_title_id,
                                                                  @manager_id,
                                                                  @effective_start_date,
                                                                  @effective_end_date,
                                                                  @action_code)
                                                              
      
              END
              ELSE 
              BEGIN
              INSERT INTO employee_assignment_history (employee_id,
                                                       department_id,
                                                       job_title_id,
                                                       manager_id,
                                                       effective_start_date,
                                                       effective_end_date ,
                                                       action_code)
                                                       VALUES (@employee_id,
                                                                  @department_id,
                                                                  @job_title_id,
                                                                  @manager_id,
                                                                  @effective_start_date,
                                                                  @effective_end_date,
                                                                  @action_code)
      
              INSERT INTO employee_assignment (employee_id,
                                                          department_id,
                                                          job_title_id,
                                                          manager_id,
                                                          effective_start_date,
                                                          effective_end_date ,
                                                          action_code)
                                                          VALUES (@employee_id,
                                                                  @department_id,
                                                                  @job_title_id,
                                                                  @manager_id,
                                                                  @effective_start_date,
                                                                  @effective_end_date,
                                                                  @action_code)
              END
      END;
  </code></pre>
  <p>However, I receive NULL row added into my tables, when I'm trying to insert a test row:</p>
  <pre><code>INSERT INTO dbo.v_employee_assignment2 (employee_id, department_id, job_title_id, manager_id, effective_start_date, effective_end_date, action_code) 
  VALUES
       (1, 2, 8, 3, '2023-06-08 00:00:00', NULL, 'MANAGER_CHANGE')
  </code></pre>
  <p>and it's supposed to be inserted into <code>employee_assignment_future</code> since the starting date is bigger than the current date, but it inserts in all the other tables but not into the <code>employee_assignment_future</code>.</p>