After using a trigger - ERROR: null value in column "group_id" violates not-null constraint

Question

I'm using PostgreSQL 8.1.23 on x86_64-redhat-linux-gnu

I have to write a database for reserving seats on language courses and there's a requirement there should be a trigger, which will check whether lector, we're trying to write into new group, has any other group at the same time. I have such table:

CREATE TABLE groups (
group_id serial PRIMARY KEY, 
lang varchar(3) NOT NULL, 
level varchar(3), 
seats int4, 
lector int4, 
start time,
day varchar(3),
FOREIGN KEY (language) REFERENCES languages(lang) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (lector) REFERENCES lectors(lector_id) ON UPDATE CASCADE ON DELETE SET NULL);

and such trigger:

CREATE FUNCTION if_available () RETURNS trigger AS '
DECLARE
r groups%rowtype;
c groups%rowtype;
BEGIN
FOR r IN SELECT * FROM groups WHERE r.lector=NEW.lector ORDER BY group_id LOOP
    IF (r.start = NEW.start AND r.day = NEW.day) THEN
        RAISE NOTICE ''Lector already has a group at this time!'';
        c = NULL;
        EXIT;
    ELSE
        c = NEW;
    END IF;
END LOOP;
RETURN c;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER if_available_t 
BEFORE INSERT OR UPDATE ON grupy
FOR EACH ROW EXECUTE PROCEDURE if_available();

After inserting the new row to a table groups, eg.:

INSERT groups (lang, level, seats, lector, start, day) values ('ger','A-2',12,2,'11:45','wed');

I get an error like this:

ERROR:  null value in column "group_id" violates not-null constraint

Without this trigger everything is OK. Could anybody help me how to make it work?

score 0 · Answer 1 · answered Jan 14 '16 at 01:00

Finally, I have solved it! After BEGIN there should be c = NEW;, because when table groups is empty at the beginning, FOR loop doesn't run and NULL is returned. Also I have changed the condition in FOR loop for: ...WHERE lector = NEW.lector.... And finally, I have changed the condition in IF for IF (r.group_id <> NEW.group_id AND r.start = NEW.start AND r.day = NEW.day) THEN..., because I haven't wanted to run this trigger before one particular update. Maybe this will be helpful for someone :)

After using a trigger - ERROR: null value in column "group_id" violates not-null constraint

1 Answers1