CODEWITHSUNDEEP
postgresqltriggerspostgresql-10
Adam
Adam

Reputation: 5599

Update row in BEFORE DELETE trigger

In short: I need to update a row in BEFORE DELETE trigger and get error:

tuple to be updated was already modified by an operation triggered by the current command

I know that there is no much sense in updating the row that will be deleted, but...

In my case I have a function to cancel an order. It does some operations including clearing the discount code from the order (so the code can be used again). I want to use the same function in a BEFORE DELETE trigger (order deletion is also a kind of cancellation).

Now I have to manually call the cancel function and then delete the order. I want to avoid the risk of accidentally deleting an order without cancelling it first, so BEFORE DELETE trigger is an obvious choice.

Is there a workaround? Any suggestions?

Why can't I update the row that is going to be deleted?

Upvotes: 0

Views: 623

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

It is a restriction caused by the implementation that keeps you from doing that. It has to do with the concept of “tuple visibility” in PostgreSQL.

I would add an additional boolean parameter modify_row that defaults to TRUE to the cancel function that determines if the row should be updated or not. Then you can call the function in your trigger with modify_row => FALSE.

Upvotes: 1

Related Questions