Thread: Trigger does not work after partitioning
Hello,
I am using Postgres 12.1 on Windows 10.
I wrote the script for the partitioning of some tables and have noticed that some triggers do not work after partitioning. In attached example, you can see that the table test1 gets a trigger that sets the field instancenbr to 1 after each insertion. Nevertheless, the field instancenbr stays to have NULL-Value.
Is it a bug or I have not taken something into account?
Thanks in advance!
Viele Grüße / Kind regards, |
Mandatory Information for business emails according to German trade laws / Pflichtangaben für geschäftliche E-mails gemäß Handelsgesetzbuch: |
Attachment
On 2020-Mar-19, Raschkowski, Michael wrote: > I am using Postgres 12.1 on Windows 10. > > I wrote the script for the partitioning of some tables and have > noticed that some triggers do not work after partitioning. In attached > example, you can see that the table test1 gets a trigger that sets the > field instancenbr to 1 after each insertion. Nevertheless, the field > instancenbr stays to have NULL-Value. This has never worked, regardless of partitioning. AFTER triggers cannot usefully change the affected row; if you want to do that, you need a BEFORE trigger. The "return NEW" line is useless in an AFTER trigger. BEFORE triggers are not supported with partitioned tables (except in the future Postgres 13, where I added that feature yesterday), but you can add the trigger to each partition and it should work correctly. Of course, you'll need to add the trigger manually on each partition you create or attach. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services