EXCEPTION not raised in trigger function triggered on DELETE FROM (postgresql 13)

I have two tables:

CREATE TABLE Registered(student TEXT, course CHAR(6))


CREATE TABLE WaitingList(student TEXT, course CHAR(6), position INT)

​x
 
CREATE TABLE Registered(student TEXT, course CHAR(6))​​CREATE TABLE WaitingList(student TEXT, course CHAR(6), position INT)​

(student, course) are primary key pairs in both tables and both student and course are individually foreign keys referenced from other tables.

I also have the view

CREATE VIEW Registrations(student, course, status)

 
CREATE VIEW Registrations(student, course, status)​

Which is a union of the two tables with status as either waiting or registered depending on which table the (student, course) pair comes from.

I then create a trigger and function:

 CREATE FUNCTION unregistration_function() RETURNS TRIGGER AS $$
    BEGIN
        IF (EXISTS(SELECT * FROM WaitingList WHERE student = OLD.student AND course = OLD.course)) THEN
              --delete OLD.student from WaitingList and then update WaitingList position;

        ELSIF (EXISTS(SELECT * FROM Registered WHERE student = OLD.student AND course = OLD.course)) THEN
              --delete OLD.student from Registered;
        
        ELSE 
              RAISE EXCEPTION 'Student % is not registered or on waitinglist for course %', OLD.student, OLD.course;

        END IF;
        RETURN OLD;
     END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER unregistration 
    INSTEAD OF DELETE FROM Registrations
    FOR EACH ROW 
    EXECUTE PROCEDURE unregistration_function();

 
 CREATE FUNCTION unregistration_function() RETURNS TRIGGER AS $$    BEGIN        IF (EXISTS(SELECT * FROM WaitingList WHERE student = OLD.student AND course = OLD.course)) THEN              --delete OLD.student from WaitingList and then update WaitingList position;​        ELSIF (EXISTS(SELECT * FROM Registered WHERE student = OLD.student AND course = OLD.course)) THEN              --delete OLD.student from Registered;                ELSE               RAISE EXCEPTION 'Student % is not registered or on waitinglist for course %', OLD.student, OLD.course;​        END IF;        RETURN OLD;     END;$$ LANGUAGE plpgsql;​CREATE TRIGGER unregistration     INSTEAD OF DELETE FROM Registrations    FOR EACH ROW     EXECUTE PROCEDURE unregistration_function();​

The trigger function works, as in it successfully unregister students from courses or remove them from the waitinglist. However, in the case where a student is not registered on a course and is not on the waitinglist for a course it does not raise the exception but still reports a successfull unregistration even though nothing was unregistered.

I’m not sure what I’m missing, as the WHERE condition should return false in the IF and ELSIF clauses in that case and the exception should be raised. As I understand it from the documentation, raising an EXCEPTION should abort the current transaction?

Is it simply that due to this being a DELETE statement, aborting the transaction causes DELETE to simply exit with 0 deleted rows which is still counted as “successful” and therefore not display my error message?

Answer

The DELETE is called for each row that is going to be deleted.

If you execute:

delete from registrations
    where student = XXX;

 
delete from registrations    where student = XXX;​

and student is not in the table or view, then the trigger is not called.

You need to look at the number of rows deleted to see if anything was deleted.

Advertisement

Answer