title: CREATE TRIGGER - SQL SERVER
body: <p>A student is going to a school where he's going to pay a monthly value. That value will be (<code>FatherSalary + MotherSalary)*0.05</code></p>
  
  <p>I only started to study triggers yesterday, I made one but i got the error </p>
  
  <blockquote>
    <p><em>Msg 515, Level 16, State 2, Procedure TR_Calc_Value, Line 25<br>
    Cannot insert the value NULL into column 'IDStudent', table 'HW32.dbo.Enrollment'; column does not allow nulls. INSERT fails.</em></p>
  </blockquote>
  
  <p>when I insert values in the table <code>Enrollment</code>. Any help how to solve this?</p>
  
  <pre><code>USE master;
  
  IF DB_ID (N'HW32') IS NOT NULL
     DROP DATABASE HW32;
  
  CREATE DATABASE HW32;
  
  USE HW32
  CREATE TABLE Family(
    IDFamily int IDENTITY(1,1),
    FirstName nchar(20) NOT NULL,
    LastName nchar(20) NOT NULL,
    Gender nchar(1)  NOT NULL,
    Salary money,
    CONSTRAINT PK_Family PRIMARY KEY(IDFamily),
    CONSTRAINT CK_Family_Gender CHECK (Gender IN ('M','F'))
  ) 
  
  CREATE TABLE Student(
    IDStudent int IDENTITY(1,1),
    FirstName nchar(20) NOT NULL,
    LastName nchar(20) NOT NULL,
    CONSTRAINT PK_Student PRIMARY KEY(IDStudent)
  )
  
  CREATE TABLE Filiation(
    IDStudent int,
    IDFamily int,
    Filiation nchar(20) NOT NULL,
    CONSTRAINT FK_Filiation_IDStudent FOREIGN KEY (IDStudent)
    REFERENCES Student(IDStudent),
    CONSTRAINT FK_Filiation_IDFamily FOREIGN KEY (IDFamily)
    REFERENCES Family(IDFamily),
    CONSTRAINT PK_Filiation PRIMARY KEY(IDStudent,IDFamily)
  )
  
  CREATE TABLE Enrollment(
    IDEnrollment int IDENTITY(1,1),
    IDStudent int NOT NULL,
    Status nchar(20) NOT NULL,
    MonthlyPayment money,
    CONSTRAINT PK_Enrollment PRIMARY KEY(IDStudent), 
    CONSTRAINT FK_Enrollment_IDStudent FOREIGN KEY (IDStudent)
    REFERENCES Student(IDStudent),
    CONSTRAINT CK_Enrollment_Status CHECK(Status IN('Acepted','Rejected')),
    CONSTRAINT UC_Enrollment UNIQUE (IDEnrollment)
  )
  
  USE HW32
  GO
  CREATE TRIGGER TR_Calc_Value 
  ON Enrollment 
  AFTER INSERT, UPDATE AS
      DECLARE @monthlyPayment money, @sFather money, @sMother money
  BEGIN
      SET @sFather = (SELECT FAM.Salary
              FROM Family FAM 
              LEFT JOIN Filiation F ON F.IDFamily = FAM.IDFamily
              LEFT JOIN inserted I ON I.IDStudent = F.IDStudent
              WHERE F.IDStudent = I.IDStudent AND FAM.Gender = 'M')
  
      SET @sMother = (SELECT FAM.Salary 
              FROM Family FAM 
              LEFT JOIN Filiation F ON F.IDFamily = FAM.IDFamily
              LEFT JOIN inserted I ON I.IDStudent = F.IDStudent
              WHERE F.IDStudent = I.IDStudent AND FAM.Gender = 'F')
  
      SET @monthlyPayment = ((@sFather + @sMother) * 0.05)
  
      INSERT INTO Enrollment (MonthlyPayment) VALUES (@monthlyPayment)
  END
  GO
  
  USE HW32
  INSERT INTO Family VALUES('John', 'Smith', 'M', 800)
  INSERT INTO Family VALUES('Anna', 'Smith', 'F', 800)
  
  INSERT INTO Student VALUES('Carl', 'Smith')
  
  INSERT INTO Filiation VALUES(1, 1, 'Father')
  INSERT INTO Filiation VALUES(1, 2, 'Mother')
  
  INSERT INTO Enrollment (IDStudent, Status) VALUES(1, 'Accepted')
  </code></pre>