Search Postgresql Archives

Cascading Trigger - changing row on delete does not delete row

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



All,

I have 2 tables which both have triggers on them. When I delete a row on table A, a cascading trigger ends up modifying rows in table B. The modified rows in table B trigger an update on rows in table A which happens to be the same row that I am trying to delete.

I don't get any errors from the delete, yet PostgreSQL tells me 0 rows affected by the delete and sure enough the row I just tried to delete is still there. Running the delete a 2nd time works because the trigger does not cascade and effect the deleted row.

Is there a way to know that a row I am deleting is being deleted so I don't update it?

I thought about adding a boolean column 'is_being_deleted' but I can't set that to true without updating the row (which I'm trying to avoid).

I've thought about using PL/Perl to access transaction-level global variables where I could store the ID of the row I'm deleting and fetch that value in order to avoid it in my updates ... but I don't want invoke the PL/Perl interpreter and slow down what I'm already doing in PL/PGSQL. Are there transaction-level variables in PL/PGSQL (globals)?

Suggestions?

-- Dante

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux