CODEWITHSUNDEEP
sqlpostgresql
user3613231
user3613231

Reputation: 33

Make a trigger stop inserting same data on different tables

I created three tables, as shown below, and I want to create a trigger such that when I try to insert a new record into table_2 while that record is already in table_3, an error message is shown and the insertion of duplicated data is prevented. This should also work the other way around, the same data can not be present in both tables.

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

Upvotes: 1

Views: 456

Answers (1)

Patrick
Patrick

Reputation: 32179

If you want to avoid inserting same data in two different tables you need to test whether the data is already in the other table when inserting. Note that you should also do this test when updating records in either of the two tables. You can do both actions, in both directions, with a single trigger function:

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;

For each of the two tables you should define a trigger that calls the trigger function:

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

Note that the trigger function would greatly benefit from the presence of indexes on table_2 and table_3.

Upvotes: 1

Related Questions