title: Trigger to update table after insert to insert or update into another table
body: <p>I am having a little trouble with my after insert trigger it does not update/insert into the table and I am not sure why.</p>
  <p>I have the tables employee, position, certification, employeecertification, positioncertification.</p>
  <p>What I need to do is after a Position is assigned a Certification then all employees that are defined in that position (Employee:PositionId = Ins.PositionId] need to get the certification added (if it does not already exist in their record) , I might need to update them where they do exist (but for the moment do not believe that will be a requirement).</p>
  <p>I have been getting a couple different results (neither is correct)</p>
  <p><em>1: EmployeeId can not insert null into EmployeeId</em></p>
  <p><em>2: On some other test data - nothing is inserted.</em></p>
  <p>I have the following trigger with the sql for creation of tables that follows:</p>
  <p><strong>The Trigger</strong></p>
  <pre><code>CREATE TRIGGER [dbo].[trig_InsNew_PositionCertification]
  ON [dbo].[PositionCertification]
  AFTER INSERT
  AS
  Insert into EmployeeCertification
  (CertificationId, EmployeeId)
  Select Ins.CertificationId, Emp.EmployeeId
  From inserted Ins
  Left Join dbo.Employee Emp On Emp.PositionId = Ins.PositionId
  Left Join dbo.Certification Trw On Trw.CertificationId = Ins.CertificationId
  
  WHERE NOT EXISTS 
  (SELECT EmployeeId, CertificationId 
  FROM 
  EmployeeCertification 
  WHERE EmployeeId = Emp.EmployeeId AND CertificationId = Ins.CertificationId);
  GO
  
  ALTER TABLE [dbo].[PositionCertification] ENABLE TRIGGER [trig_InsNew_PositionCertification]
  GO
  </code></pre>
  <p><strong>CREATE TABLES:</strong></p>
  <pre><code>    CREATE TABLE [dbo].[Position](
          [PositionId] [int] IDENTITY(1,1) NOT NULL,
          [Name] [nvarchar](50) NOT NULL,
       CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED 
      (
          [PositionId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
      GO
      
      CREATE TABLE [dbo].[Employee](
          [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
          [EmployeeName] [nvarchar](512) NOT NULL,
          [PositionId] [int] NOT NULL,
      
       CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
      (
          [EmployeeId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]
      GO
      ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Position] FOREIGN KEY([PositionId])
      REFERENCES [dbo].[Position] ([PositionId])
      GO
      
      ALTER TABLE [dbo].[Position] CHECK CONSTRAINT [FK_Employee_Position]
      GO
      
      
      CREATE TABLE [dbo].[Certification](
          [CertificationId] [int] IDENTITY(1,1) NOT NULL,
          [Name] [nvarchar](512) NOT NULL,
      
       CONSTRAINT [PK_Certification] PRIMARY KEY CLUSTERED 
      (
          [CertificationId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]
      GO
      
      
      CREATE TABLE [dbo].[EmployeeCertification](
          [EmployeeCertificationId] [int] IDENTITY(1,1) NOT NULL,
          [EmployeeId] [int] NOT NULL,
          [CertificationId] [int] NOT NULL,
      
       CONSTRAINT [PK_EmployeeCertification] PRIMARY KEY CLUSTERED 
      (
          [EmployeeId] ASC,
          [CertificationId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
      GO
      
      
      ALTER TABLE [dbo].[EmployeeCertification]  WITH CHECK ADD  CONSTRAINT [FK_EmpCertification_Certification] FOREIGN KEY([CertificationId])
      REFERENCES [dbo].[Certification] ([CertificationId])
      GO
      
      ALTER TABLE [dbo].[EmployeeCertification] CHECK CONSTRAINT [FK_EmpCertification_Certification]
      GO
      
      ALTER TABLE [dbo].[EmployeeCertification]  WITH CHECK ADD  CONSTRAINT [FK_EmpCertification_Employee] FOREIGN KEY([EmployeeId])
      REFERENCES [dbo].[Employee] ([EmployeeId])
      GO
      
      ALTER TABLE [dbo].[EmployeeCertification] CHECK CONSTRAINT [FK_EmpCertification_Employee]
      GO
  
      CREATE TABLE [dbo].[PositionCertification](
      [PositionCertificationId] [int] IDENTITY(1,1) NOT NULL,
      [PositionId] [int] NOT NULL,
      [CertificationId] [int] NOT NULL,
  
   CONSTRAINT [PK_PositionCertification] PRIMARY KEY CLUSTERED 
  (
      [PositionId] ASC,
      [CertificationId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO
  </code></pre>