I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if the room number of the new event will be busy for the time of the new event. If so then I want to raise an exception else insert the new event into the table. I thought to declare the trigger as
instead of trigger for
insert but postgres doesn't allow
instead of triggers on
insert operation on tables (and I don't want to create a view just for the trigger).
How can I work around this problem? Theoretically I could create an
after trigger and then check if the newly inserted data is valid and delete if it's not. But this seems like a wrong approach conceptually and maybe practically as well (I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data).
This is the table definition:
create table room_schedule( start_date date, start_time time, room_no int, event_id int, duration interval, primary key(start_date, start_time, room_no, event_id) );
This is my trigger and function definitions:
create or replace function inserttrigfunc() returns trigger as $$ declare count int; begin with end_time_table(eid, stime, etime) as ( select event_id, start_time, (start_time + duration) as etime from room_schedule where room_no=new.room_no ), overlapping_time_table(eid, stime, etime) as( select * from end_time_table where (stime <= new.start_time and new.start_time <= etime) or (stime <= (new.start_time + new.duration) and (new.start_time + new.duration) <= etime) ) select count(*) from overlapping_time_table into count; if count > 0 then begin raise exception 'the room is already occupied at the time'; end; else begin insert into room_schedule(start_date, start_time, room_no, event_id, duration) values(new.start_date, new.start_time, new.room_no, new.event_id, new.duration); end; end; $$language plpgsql; create trigger TRIG1 instead of insert on room_schedule for each row execute procedure inserttrigfunc();