CODEWITHSUNDEEP
postgresqltriggers
Dao Lam
Dao Lam

Reputation: 2937

How to store values in trigger PostgreSQL

I have a trigger that looks something like this:

CREATE OR REPLACE FUNCTION CHECK_SCHEDULE()
RETURNS TRIGGER AS
$BODY$
BEGIN
  IF EXISTS(
    SELECT DAY, TIME FROM MEETING
    WHERE NEW.DAY = MEETING.DAY AND NEW.TIME > MEETING.TIME
    ) THEN
    RAISE EXCEPTION 'THERE IS A MEETING HAPPENING ON % % ', NEW.DAY, NEW.TIME;
  ELSE
    RETURN NEW;
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL;

This works fine except I want the message to be the time it's conflicting with: There is a meeting happening on MEETING.DAY and MEETING.TIME.

However I cannot do this because it doesn't know what these variables are. Is it possible to store the values in my select clause so I can use them later?

Upvotes: 0

Views: 1027

Answers (1)

user1322772
user1322772

Reputation:

You can move the day and time into a declared variable (e.g. a RECORD) for reference later.

CREATE OR REPLACE FUNCTION CHECK_SCHEDULE()
RETURNS TRIGGER AS
$BODY$
DECLARE

    meetinginfo RECORD;

BEGIN

    SELECT meeting.day, meeting.time
    INTO meetinginfo
    FROM meeting
    WHERE new.day = meeting.day
    AND new.time > meeting.time
    ORDER BY new.time
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION 'THERE IS A MEETING HAPPENING ON % %', meetinginfo.day, meetinginfo.time;
    END IF;

    RETURN NEW;

END;
$BODY$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions