postgresql - 如何创建检查值的触发器？

Question

我的触发器需要帮助。例如我有这样的查询

INSERT INTO test(id_users,id_type,id_color) VALUES(1,3,4);

在我的数据库中，我有一个名为:test 的表

ghost, id, id_users, id_type, id_color

我需要在插入或更新之前进行检查

Create trigger
Begin
select id from test where ghost = false and id_users = 26 and id_type = 3
if NO execute INSERT
if YEST exit with no action
END

我怎样才能创建这个触发器？

Answer 1

有两种方法，具体取决于您希望如何管理问题。

如果你想让它静音，使用一个before触发器并返回null:

create function ignore_invalid_row() returns trigger as $$
begin
  if not exists(
     select 1
       from test
      where not ghost
        and id_users = new.id_users
        and id_type = new.id_type
     )
  then
    return null;
  end if;

  return new;
end;
$$ language plpgsql;

create trigger ignore_invalid_row_tg before insert on test
for each row execute procedure ignore_invalid_row();

如果你想引发它，使用约束触发器并引发异常:

create function reject_invalid_row() returns trigger as $$
begin
  if not exists(
     select 1
       from test
      where not ghost
        and id_users = new.id_users
        and id_type = new.id_type
     )
  then
    raise exception 'message';
  end if;

  return null;
end;
$$ language plpgsql;

create constraint trigger reject_invalid_row_tg after insert on test
for each row execute procedure reject_invalid_row();

http://www.postgresql.org/docs/current/static/sql-createtrigger.html