CODEWITHSUNDEEP
sqlpostgresqlfunctiontriggers
Amos Cappellaro
Amos Cappellaro

Reputation: 35

Function for trigger (INSTEAD OF DELETE ON view) can't pick values from query [POSTGRESQL]

The function and relative trigger I made are as it follows:

CREATE OR REPLACE FUNCTION unregister() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Student: %, course: %.', NEW.student, NEW.course;
    IF (EXISTS (SELECT student FROM view_registered WHERE student = NEW.student AND course = NEW.course)) THEN
      DELETE FROM table_registered WHERE student = NEW.student AND course = NEW.course;
      RAISE NOTICE '% successfully unregistered from this course (%)!', NEW.student, NEW.course;
    ELSE
      RAISE EXCEPTION '% is not registered to this course (%)!', NEW.student, NEW.course;
    END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER unregister INSTEAD OF DELETE ON view_registered
    FOR EACH ROW EXECUTE PROCEDURE unregister();

Since the beginning, I saw through the initial RAISE NOTICE that the function is not picking values from queries like:

DELETE FROM view_registered WHERE student = 4002222222 AND course = 'ITC222';

In fact, the output of it starts with NOTICE: Student: <NULL>, course: <NULL>.

I can't understand what's wrong with this code and why the function picks the values from queries.

Upvotes: 1

Views: 441

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I think you want OLD:

RAISE NOTICE 'Student: %, course: %.', OLD.student, OLD.course;

In a delete trigger, OLD is populated but NEW is not.

Upvotes: 1

Related Questions