CODEWITHSUNDEEP
postgresql
ILYA Khlopotov
ILYA Khlopotov

Reputation: 725

Using triggers to maintain linking table

I'm considering employing triggers for maintaining linking table. However my initial approach fails due to foreign key constraint violation. Is there any way to solve the issue without disabling constraints?


CREATE TABLE foo (
  id  SERIAL PRIMARY KEY,
  data TEXT
);
CREATE TABLE bar (
  id  SERIAL PRIMARY KEY,
  data TEXT
);
CREATE TABLE foo_bar_link (
  foo_id     INT NOT NULL REFERENCES foo(id),
  bar_id     INT NOT NULL REFERENCES bar(id),
  UNIQUE (foo_id, bar_id)
);
CREATE OR REPLACE FUNCTION maintain_link()
RETURNS TRIGGER AS
$maintain_link$
DECLARE
  bar_id INT;
BEGIN
  INSERT INTO bar (data) VALUES ('not_important_for_this_example_bar_data') RETURNING id INTO bar_id;
  INSERT INTO foo_bar_link (foo_id, bar_id) VALUES (NEW.id, bar_id);
  RETURN NEW;
END;
$maintain_link$
LANGUAGE plpgsql;

CREATE TRIGGER maintain_link BEFORE INSERT ON foo
    FOR EACH ROW EXECUTE PROCEDURE maintain_link();

Here is sqlfiddle.

Upvotes: 0

Views: 76

Answers (1)

mrlindsey
mrlindsey

Reputation: 310

Use AFTER insert, since using BEFORE insert fails because your parent row in foo doesn't exist yet.

Upvotes: 1

Related Questions