现在，我编写的触发器存在以下问题：如果我插入的新行与表Weeklymeeting中的一个条目发生冲突，则不应将其插入表中并给出错误消息。如果NEW行与表不冲突，则应将新行插入表中。但是下面的代码在时间冲突时会给我错误，而在没有冲突时却无法在表中插入新行。以下触发器的问题出在哪里？如何解决这个问题？
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();
根据比约恩·尼尔森（BjörnNilsson）的评论，我的问题得到解决。正确的解决方案将是：
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();
无需触发：
ALTER TABLE weeklymeeting ADD CONSTRAINT section_weekday_unique_constraint UNIQUE (section, weekday);
这将在这两列上创建一个索引，因此您可能希望根据查询数据的方式来反转它们的顺序。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句