如何在 postgres 的表上創建“而不是”觸發器？

我想為 postgresql 中的表創建一個觸發器。我的表包含有關事件的數據，包括房間號、開始時間和事件持續時間。在新插入表格後，我想檢查新活動的房間號在新活動期間是否很忙。如果是這樣，那麼我想引發一個異常，否則將新事件插入表中。我想將觸發器聲明為instead of觸發器，insert但 postgres 不允許instead of觸發器對insert錶的操作（而且我不想僅為觸發器創建視圖）。

我該如何解決這個問題？理論上我可以創建一個after觸發器，然後檢查新插入的數據是否有效，如果不是則刪除。但這在概念上和實踐上似乎都是錯誤的方法（我不確定觸發器是否是原子的，也許有人已經根據不良數據做出了錯誤的決定）。

這是表定義：

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)
);

這是我的觸發器和函式定義：

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();

您可以使用正常的行級觸發器來實現您想要的。如果該觸發器引發異常，insert則不會發生。如果new從觸發器返回，插入將繼續。

從手冊中引用

如果返回非空值，則操作將繼續使用該行值。返回與 NEW 的原始值不同的行值會改變將被插入或更新的行。因此，如果觸發函式希望觸發動作在不改變行值的情況下正常成功，則必須返回 NEW（或與其相等的值）

（強調我的）

基本上是這樣的：

if count > 0 then
  raise exception 'the room is already occupied at the time';
else 
  return new;
end if;

然後將觸發器定義為：

create trigger TRIG1 before insert
 on room_schedule
 for each row
 execute procedure inserttrigfunc();

我不確定觸發器是否是原子的，也許有人已經根據不良數據做出了錯誤的決定

是的，它們是並且它們是觸發觸發器的事務的一部分。

---

但是你根本不需要觸發器。

這可以通過使用兩個日期作為日期範圍的排除約束輕鬆實現

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),
 EXCLUDE USING gist (room_no with =, daterange(start_date, end_date, '[]') WITH &&)
);

請注意，您需要上述的擴展 btree gist，因為預設情況下，GiST 索引不支持相等性，因為約束只應適用於相同的room_no

引用自：https://dba.stackexchange.com/questions/212864