postgresql trigger for insert

Question

Now the trigger I write has the following problem: if the new row I insert is conflict with one entry in the table weeklymeeting, it should not insert into table and give me error message. While if the NEW row is not conflict with the table, the new row should insert into the table. But the code below when time conflict, it give me error while when not conflict, it cannot insert new row into table. Where is the problem for the below trigger. how to fix this?

    DROP FUNCTION IF EXISTS time_conflict() CASCADE;
create or replace function time_conflict() 
returns trigger as 
$BODY$
begin   
    if exists(  
        select *
        from weeklymeeting d

        where NEW.section_id=d.section_id 
        AND NEW.weekday= d.weekday 
        AND ((d.starttime <= NEW.starttime AND d.endtime > NEW.starttime) OR (d.starttime < NEW.endtime AND d.endtime >= NEW.endtime) OR (d.starttime >=NEW.starttime AND d.endtime <=NEW.endtime )) 
        )THEN
           RAISE EXCEPTION 'SAME section time conflict!';
        else
        INSERT INTO weeklymeeting VALUES (NEW.*); 
        end if; 
        RETURN NEW;

end;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER time_conflict
BEFORE INSERT ON weeklymeeting for each ROW
EXECUTE PROCEDURE time_conflict();

Base on the comment from Björn Nilsson my problems fixed. the right solution will be like:

DROP FUNCTION IF EXISTS time_conflict() CASCADE;
create or replace function time_conflict() 
returns trigger as 
$BODY$
begin   
    if exists(  
        select *
        from weeklymeeting d

        where NEW.section_id=d.section_id 
        AND NEW.weekday= d.weekday 
        AND ((d.starttime <= NEW.starttime AND d.endtime > NEW.starttime) OR (d.starttime < NEW.endtime AND d.endtime >= NEW.endtime) OR (d.starttime >=NEW.starttime AND d.endtime <=NEW.endtime )) 
        )THEN
           RAISE EXCEPTION 'SAME section time conflict!';

        end if; 
        RETURN NEW;

end;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER time_conflict
BEFORE INSERT ON weeklymeeting for each ROW
EXECUTE PROCEDURE time_conflict();

the new row is inserted from jsp page. it has the same columns as table weeklymeeting. — user3382017, Mar 14 '14 at 01:55
Any description for conflicting time ranges? If you actually want to check for *overlapping* time ranges, [this related answer](http://stackoverflow.com/questions/4480715/find-overlapping-date-ranges-in-postgresql/15305292#15305292) may be of help. For mutually exclusive weekly time ranges, [look here.](http://stackoverflow.com/questions/22108477/native-way-of-performing-this-hours-of-operation-query-in-postgresql/22111524#22111524) — Erwin Brandstetter, Mar 14 '14 at 04:24

score 1 · Accepted Answer · answered Mar 14 '14 at 01:59

1

No need for a trigger:

ALTER TABLE weeklymeeting ADD CONSTRAINT section_weekday_unique_constraint UNIQUE (section, weekday);

This creates an index on those two columns, so you might want to reverse the order of them, depending how you query the data

answered Mar 14 '14 at 01:59

Björn Nilsson

3,703
1
24
29

I need to use trigger because it is required by professor – user3382017 Mar 14 '14 at 02:04
Ok, well, you should return NEW, otherwise it won't work since it is a before trigger. Then you don't need to do the insert manually. – Björn Nilsson Mar 14 '14 at 02:05
Did you see my edit on the comment? Don't do a manual insert, just return NEW – Björn Nilsson Mar 14 '14 at 02:09
I think doing a manual insert will cause the trigger to be fired again. – Björn Nilsson Mar 14 '14 at 02:10

postgresql trigger for insert

1 Answers1