CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Terry Yen
Terry Yen

Reputation: 25

Pass the inserted parameter to a trigger function

I am trying to create a function/trigger that work between two tables, courses and enrollment, using postgresql. When a new row is inserted in the enrollment table, the trigger should update the corresponding enrolled value in the courses table and set the open stats to false if it is full.

I tried to select the corresponding course from the enrollment table and update the corresponding table using the following codes:

Tables:

create table students (
    id integer primary key,
    name varchar(24)
);

create table courses (
    num varchar(6) primary key,
    open boolean not null,
    enrolled integer default 0,
    lim integer default 3
);


create table enrollment (
    student integer references students(id),
    course varchar(6) references courses(num)
);

function and trigger:

create or replace function func_1() returns trigger as
  $$
  declare
    enrol integer;
    limt integer;
  begin
        select enrolled into enrol from courses where num = (select course from enrollment where course = new.course);
        select lim into limt from courses where num = (select course from enrollment where course = new.course);
        if enrol<limt then
            update courses set enrolled = enrol+1 where num = (select course from enrollment where course = new.course);
            if enrol = limt-1 then
                update courses set open = False where num = (select course from enrollment where course = new.course);
            end if;
        end if;
        return new;
  end;
  $$ language plpgsql;

create trigger trigger_1
  after insert
  on enrollment
  execute procedure func_1();

Assume the following data were inserted to the students and courses table:

insert into students values(1, 'Alice');
insert into students values(2, 'Bob');
insert into courses values('CS1555', True);
insert into courses values('CS1501', True);

When I run this:

insert into enrollment values(1, 'CS1501');

It inserted into the enrollment table but it does not update the corresponding rows in the courses table, the enrolled valued did not get +1.

I have also tried to replace

where num = (select course from enrollment where course = new.course)

to

where num in (select course from enrollment)

in the trigger, but if I did that it will update all rows in the courses table (all enrolled values got +1), not the corresponding one.

Please let me know how could I update the corresponding values correctly, thanks!

Upvotes: 0

Views: 727

Answers (1)

FXD
FXD

Reputation: 2060

First things first: a student should not be able to enroll several times to the same course + it makes no sense to have a student enroll to no course or a null student enroll to a course (unless you have a good reason for it).
UNIQUE + NOT NULL to be defined. Let's use a primary key.

create table enrollment (
    student integer references students(id),,
    course varchar(6) references courses(num),
    PRIMARY KEY (student, course)
);

In term of DB normalization, your approach is not good: enrolled does not depend (only) on the key.
The best solution is to calculate it:

SELECT courses.*,
(SELECT COUNT(*) FROM enrollment WHERE enrollment.course = courses.num) AS enrolled
FROM courses

Alternatively, you can do the insert and update the table in 1 statement:

WITH InsertStatement AS (
INSERT INTO enrollment VALUES(1, 'CS1501')
RETURNING course
)
UPDATE courses
SET enrolled = enrolled + AddedStudents
FROM (SELECT course, count(*) AS AddedStudents FROM InsertStatement GROUP BY course) Aux
WHERE courses.num = Aux.course

I can elaborate a bit about why your trigger is not working.
The default behavior for a trigger is to execute after each statement. Your code requires FOR EACH ROW to be specified.

Redefine your trigger and see if it solves your problem. However, I do NOT guarantee this will work with concurrent inserts (that is exactly why 80% of my answer is about avoiding the use of triger).

Upvotes: 1

Related Questions