DuckDB 2.0 alpha's triggers cost three times more per row than per statement
DuckDB's preview post for version 2.0 promised triggers with "BEFORE and AFTER triggers, FOR EACH ROW and FOR EACH STATEMENT". I downloaded the alpha build and tried all eight combinations against INSERT, UPDATE and DELETE. Five of them don't work yet, and the one that's missing most completely is BEFORE, which is the half of the feature most people reach for first, to validate or reject a row…
DuckDB 2.0 alpha, codenamed Cyanoptera, introduced a preview of triggers with BEFORE, AFTER, and FOR EACH ROW and FOR EACH STATEMENT options. The alpha binary can parse CREATE TRIGGER, but not all combinations of trigger types are implemented. BEFORE FOR EACH ROW triggers are not yet supported for INSERT, UPDATE, and DELETE operations, while AFTER FOR EACH ROW triggers work for INSERT and DELETE but not for UPDATE.
Statement-level triggers work for all three events (BEFORE, AFTER, and ALL) without issues. Mixing FOR EACH STATEMENT and FOR EACH ROW triggers on the same table is not supported, and the DROP TRIGGER command has a syntax error. Bulk inserts with statement-level triggers are faster than row-level triggers, which consume more resources due to more trigger invocations.
The transition table for row-level triggers behaves as described in the documentation. Recursive triggers are not yet supported and will result in a cycle detection error. The preview post also claimed a rewritten recursive CTE engine, but the author could not observe any measurable performance improvement over the previous version.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
