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?
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
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 $$
IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
RAISE NOTICE 'Cannot delete order that is partially or completely done.';
$$ 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.