sql - 使触发器停止在不同的表上插入相同的数据

Question

我创建了三个表，如下所示，我想创建一个触发器，以便在我尝试向 table_2 中插入一条新记录时而该记录已经在 table_3 中，将显示一条错误消息并防止插入重复数据。这也应该反过来，相同的数据不能出现在两个表中。

create table table_1 (
  a varchar(255),
  b integer,
  d varchar(255),
  primary key(a, b)
);

create table table_2 (
  a varchar(255),
  b integer,
  c varchar(255),
  primary key(a, b, c),
  foreign key(a, b) references table_1(a, b)
);

create table table_3 (
  a varchar(255),
  b integer,
  c varchar(255),
  primary key(a, b, c),
  foreign key(a, b) references table_1(a, b)
);

Answer 1

如果你想避免在两个不同的表中插入相同的数据，你需要在插入时测试数据是否已经在另一个表中。请注意，在更新两个表中的任何一个中的记录时，您也应该进行此测试。您可以使用一个触发函数在两个方向上执行两个操作:

CREATE FUNCTION check_duplicates_in_tables_2_3() RETURNS trigger AS $$
BEGIN
  -- Check if the new data is in either of the two tables, works for inserts and updates
  PERFORM * FROM table_2 WHERE a = NEW.a AND b = NEW.b AND c = NEW.c;
  IF FOUND THEN
    RAISE 'Data is already present in table_2';
  END IF;
  PERFORM * FROM table_3 WHERE a = NEW.a AND b = NEW.b AND c = NEW.c;
  IF FOUND THEN
    RAISE 'Data is already present in table_3';
  END IF;

  -- Data is unique so let the insert or update operation succeed.
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

对于这两个表中的每一个，您都应该定义一个触发器来调用触发器函数:

CREATE TRIGGER table_2_duplicate_check
  BEFORE INSERT OR UPDATE ON table_2
  FOR EACH ROW EXECUTE PROCEDURE check_duplicates_in_tables_2_3();

CREATE TRIGGER table_3_duplicate_check
  BEFORE INSERT OR UPDATE ON table_3
  FOR EACH ROW EXECUTE PROCEDURE check_duplicates_in_tables_2_3();

请注意，table_2 和 table_3 上存在索引会极大地受益于触发器函数。