CODEWITHSUNDEEP
postgresqldatabase-trigger
Marin Leontenko
Marin Leontenko

Reputation: 771

Postgres - delete record after trigger

I have a postgres table with some triggers that fire if records are udpated or deleted. They basically archive the records so they are not trully deleted - they just change certain attributes so the records are not displayed in corresponding views.

Sometimes I want to manually delete a record for good, but I can't do it because the trigger fires and does it's thing if I execute DELETE query.

Example: DELETE FROM records WHERE operator = 20

Is there a way to run DELETE query and bypass a trigger that fires on DELETE?

Upvotes: 1

Views: 480

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

With a setup like this, I think the typical approach is to avoid granting any direct privileges on the underlying table, and put your INSERT / UPDATE / DELETE triggers on the view (allowing the table owner to change it as needed).

If you are the table owner, you can use ALTER TABLE to temporarily DISABLE and re-ENABLE the trigger. As long as you do all of this within a transaction, you'll be safe against concurrent DELETEs, though they'll block until your transaction commits.

If you have superuser privileges, you can also prevent triggers from firing by setting session_replication_role to replica, provided that the trigger in question hasn't been configured to fire on replicated databases.

Upvotes: 3

Related Questions