4

I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?

0

1 Answer 1

3

For that you can use Foreign keys (referential integrity constraints):

REFERENCES orders ON DELETE CASCADE,

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row.

See the manual

I explain it a bit better.

You have a parent table A with an id, and you have a second table B, which has a reference to the id in Table A with CASCADE DELETE.

As the check constraints don't support referencing other tables, you can use a trigger with a function:

CREATE FUNCTION delete_tabentry () RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_child
  BEFORE DELETE ON child
  FOR EACH ROW EXECUTE PROCEDURE delete_tabentry();

This would prevent deletion from child until its parent record is deleted.

0

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .