0

i need to insert an "exam entry" row into a table.

But the exam entry cannot be inserted if a student (recognised by the student number sno) is already entered into that exam (recognised by the exam code excode), and the entry also cannot be inserted if the student has more than one exam on the same day (i have an exam table holding the information abotu the exam dates).

I am fairly certain that i should be using an insert trigger function for this and have been looking at:

Example 39-3 from http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

so far i have:

INSERT INTO
entry(excode, sno, egrade) VALUES (2, 1, 98.56)

CREATE FUNCTION  entry_insert() RETURNS trigger AS $entry_insert$
BEGIN
--Check student is not entered into same exam twice
IF BLA BLA

RAISE EXCEPTION 'A student cannot be be entered into the same exam more than once'; 
END IF;
--Check student not taking more than one exam on same day
IF BLA BLA

RAISE EXCEPTION 'A student cannot take more than one exam on the same day';
END IF;

END;
$entry_insert$ LANGUAGE PLPGSQL;

CREATE TRIGGER entry_insert BEFORE INSERT ON entry
FOR EACH ROW EXECUTE PROCEDURE entry_insert();

the places where I've put bla bla is where i need the conditions that i cant quite figure out how to meet my conditions.

would love some help?

edit: my exam table 

CREATE TABLE exam (
excode       CHAR(4) NOT NULL PRIMARY KEY,
extitle      VARCHAR(20) NOT NULL,
exlocation   VARCHAR(20) NOT NULL, --I'm assuming that an exam will have a location confirmed prior to insertion into the table--
exdate       DATE NOT NULL
        CONSTRAINT incorrectDate
        CHECK (exdate >='01/06/2015' AND  exdate <= '30/06/2015'),  /* I'm assuming that all exams must have a date confirmed or otherwise the exam wouldn't be inserted into the table*/
extime       TIME NOT NULL, -- I'm assuming that an exam will have a time confirmed prior to insertion into the table--
        CONSTRAINT incorrect_time 
        CHECK (extime BETWEEN '09:00:00' AND '18:00:00')
);
Improve this question
2
  • If you'd like the code somewhat written I'm going to have to see more of your data layout-- IE the table that stores the date of the exams they're enrolled in. Commented Dec 5, 2014 at 19:20
  • @JoeLove edited to show exam table Commented Dec 6, 2014 at 18:04

1 Answer 1

Reset to default
1

You don't need to use triggers for this, you can use normal table constraints, although you will need to define a function.

Your first requirement - that the same student cannot enter the same exam twice - can be checked using a UNIQUE constraint on (excode,sno). In theory this check is redundant because the second check (that a student cannot enter more than one exam per day) would also be violated by that. However, to cater for the possibility of subsequent record updates, this UNIQUE constraint is still needed.

The second requirement can be met using a CHECK constraint. However you have to create a function, because it is not possible to use a subquery inside a CHECK constraint.

Here is an example:

-- Assume we have an exams table. This table specifies the time of each exam
CREATE TABLE exams(excode SERIAL PRIMARY KEY, extime timestamp);

-- Create the entry table. We cannot add the CHECK constraint right away
-- because we have to define the function first, and the table must exist
-- before we can do that.
CREATE TABLE entry(excode int, sno int, egrade FLOAT, UNIQUE(excode,sno));

-- Create a function, which performs a query to return TRUE if there is
-- another exam already existing which this student is enrolled in that
-- is on the same day as the exame identified with p_excode
CREATE FUNCTION exam_on_day(p_excode int, p_sno int) RETURNS bool as $$
SELECT TRUE 
FROM entry 
    LEFT JOIN exams ON entry.excode=exams.excode 
WHERE sno=p_sno AND entry.excode != p_excode
    AND date_trunc('day',extime)=(
        SELECT date_trunc('day', extime) FROM exams WHERE excode=p_excode
    );
$$ LANGUAGE SQL;

-- Add check constraint
ALTER TABLE entry ADD CONSTRAINT exam_on_same_day 
    CHECK(not exam_on_day(excode, sno));

-- Populate some exames.
-- excode 1
INSERT INTO exams(extime) VALUES('2014-12-06 10:00');
-- excode 2
INSERT INTO exams(extime) VALUES('2014-12-06 15:00');
-- excode 3
INSERT INTO exams(extime) VALUES('2014-12-05 15:00');

Now we can try it out:

harmic=> INSERT INTO entry(excode,sno,egrade) VALUES(1,1,98.5);
INSERT 0 1

harmic=> INSERT INTO entry(excode,sno,egrade) VALUES(1,1,50);
ERROR:  duplicate key value violates unique constraint "entry_excode_sno_key"
DETAIL:  Key (excode, sno)=(1, 1) already exists.

harmic=> INSERT INTO entry(excode,sno,egrade) VALUES(2,1,99);
ERROR:  new row for relation "entry" violates check constraint "exam_on_same_day"
DETAIL:  Failing row contains (2, 1, 99).

harmic=> INSERT INTO entry(excode,sno,egrade) VALUES(3,1,75);
INSERT 0 1

harmic=> UPDATE entry SET egrade=98 WHERE excode=1 AND sno=1;
UPDATE 1

test=> UPDATE entry SET excode=2 WHERE excode=3 AND sno=1;
ERROR:  new row for relation "entry" violates check constraint "exam_on_same_day"
DETAIL:  Failing row contains (2, 1, 75).

Note that I named the constraint meaningfully, so that when you get an error you can see why (useful if you have more than one constraint).

Also note that the function used for the SELECT constraint excludes the record being updated from the check (entry.excode != p_excode) otherwise you could not update any records.

You could still do this with triggers, of course, although it would be unnecessarily complicated to set up. The IF condition would be similar to the function created above.

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

3 Comments

I prefer constraint triggers, personally, as they can be made deferrable, while check constraints cannot. The syntax is different, of course, but it's more flexible.
hello, just got round to looking at your answer @harmic helped me out a lot
@harmic so I'm at the point of testing my data, and i have realised that i cannot update an entry with this method because it returns the error specified for the on the same day, any clue's how i would go about editing this?