Postgresql Trigger Not Firing

Question

I have a function and a trigger as below:

CREATE FUNCTION update() RETURNS TRIGGER AS $logupdate$ 
DECLARE
judge boolean;
BEGIN 
judge := EXECUTE ('SELECT starttime,endtime,NEW.starttime,NEW.endtime FROM reserves WHERE bid = NEW.bid AND startdate = NEW.startdate AND (starttime,endtime) overlaps(NEW.starttime,NEW.endtime) IS NULL'); 
IF judge = f THEN RETURN false; 
END IF;
RETURN NEW; 
END;
$logupdate$ LANGUAGE plpgsql;

CREATE TRIGGER logupdate 
before UPDATE ON reserves 
FOR EACH ROW 
EXECUTE PROCEDURE update();

The trigger should detect if the new input has overlapping with previous time, for example if there is an old time in my table [11:00 - 13:00], and a new input is [12:00 - 14:00] then the trigger should fire at it and stop insertion. However, it doesn't work in my computer, what's wrong here?

Falmarri · Accepted Answer · 2016-02-17 01:32:26Z

1

Your trigger gets executed for every row. You don't need to query the tables. You already have access to the old and new rows.

CREATE FUNCTION update() RETURNS TRIGGER AS $logupdate$ 
BEGIN 
IF (NEW.starttime, NEW.endtime) OVERLAPS (OLD.starttime, OLD.endtime) THEN 
RETURN false; 
END IF;
RETURN NEW; 
END;
$logupdate$ LANGUAGE plpgsql;

However, this is super inefficient. You should consider using an exclusion constraint http://www.postgresql.org/docs/9.0/static/ddl-constraints.html

I'm pretty sure there's an exclusion constraint that will work with OVERLAPS

answered Feb 17, 2016 at 1:32

Falmarri

48.7k43 gold badges158 silver badges196 bronze badges

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

2 Comments

Tom Dawn Over a year ago

thx for advice, however I get the following 'ERROR: record "old" is not assigned yet' Is there a way to fix this?

Falmarri Over a year ago

Oh, sorry. OLD actually isn't defined since it's a BEFORE UPDATE trigger. My mistake. If you're only looking to do this on updates, and only care if the update overlaps with the previous value, you might want to change it to an AFTER update and throw an exception if it fails. Otherwise I would really just use an exclusion constraint stackoverflow.com/questions/10616099/…

Collectives™ on Stack Overflow

Postgresql Trigger Not Firing

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related