CREATE TRIGGER - SQL SERVER

Question

A student is going to a school where he's going to pay a monthly value. That value will be (FatherSalary + MotherSalary)*0.05

I only started to study triggers yesterday, I made one but i got the error

Msg 515, Level 16, State 2, Procedure TR_Calc_Value, Line 25
Cannot insert the value NULL into column 'IDStudent', table 'HW32.dbo.Enrollment'; column does not allow nulls. INSERT fails.

when I insert values in the table Enrollment. Any help how to solve this?

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')

Is there a reason for storing this information. It seems like a view could perform this calculation whenever you need to query for it. Also, your trigger is broken - inserted is a pseudo-table. It may contain multiple rows. As such, assigning the result of a query using it to a scalar variable is almost always wrong. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 2, 2012 at 12:23
See that INSERT in your trigger: the two NOT NULL columns IDStudent and Status in the Enrollment table aren't being filled -- that's what's causing the error message. You define them as NOT NULL and you don't define any defaults on them - in that case, you MUST provide values for those two columns in every INSERT statement into the Enrollment table — marc_s
– marc_s, Commented Dec 2, 2012 at 12:31
A friend gave this trigger last night but he just sent me the code through email and didn't explained me how to use it, i made a few changes but I didn't know what that inserted was. The solution is below, thank you for the answear and thank's to the user below for the solution. — wormwood87
– wormwood87, Commented Dec 2, 2012 at 12:37

BellevueBob · Accepted Answer · 2012-12-02 13:57:12Z

6

You don't want to INSERT a row in your trigger - the rows have already been added. Instead, you want to perform an UPDATE:

CREATE TRIGGER TR_Calc_Value ON Enrollment AFTER INSERT, UPDATE AS
BEGIN
   UPDATE E 
   set MonthlyPayment = (

      select 0.05 * SUM(Salary) 
      from Family FAM 
      inner join Filiation F 
      on FAM.IDFamily= F.IDFamily 
      where F.IDStudent = I.IDStudent
      )

   from Enrollment E
   inner join inserted I
   on E.IDEnrollment=  I.IDEnrollment

Things to note

The above properly copes with inserted containing multiple rows
I don't distinguish between father and mother (since they seem to be treated identically)
If there are multiple fathers and mothers associated with the student, they all get included. Whether this is right or not is debatable, and with better constraints may not be possible - but the original trigger doesn't cope with this situation at all well either.

As per my comment, I'd also recommend not storing this information if it can always be recalculated from the other tables (not sure if that's correct in this case, but if it should reflect the other tables, you'll needs lots more triggers to keep everything consistent.

And as per-marc's deleted answer, it would also be a good habit to get into for you to always specify a column list when doing inserts. It's not the cause of this particular error, but helps to document your intentions better and helps to quickly eliminate possible errors by visual inspection.

edited Dec 2, 2012 at 13:57

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

answered Dec 2, 2012 at 12:26

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

Furthermore - when an INSERT was supposed to happen - the two NOT NULL columns IDStudent and Status in the Enrollment table wouldn't be filled by that statement (and that's what's causing the error message in the first place)

Collectives™ on Stack Overflow

CREATE TRIGGER - SQL SERVER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related