When trigger returns NULL, row is not inserted, but result of the operation is still reported as successful (INSERT 0 0). And in my opinion this is not logical, as one would probably want to handle this properly. I wonder if there's a way to throw an actual error from the trigger?
- 1Please add the actual trigger to show us what are you trying to achieve. And tag your PostgreSQL version.McNets– McNets2020-06-12 08:29:42 +00:00Commented Jun 12, 2020 at 8:29
Add a comment |
1 Answer
If the trigger function is written in PL/pgSQL, then you can raise errors with RAISE:
CREATE FUNCTION my_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF random() < 0.01 THEN RAISE EXCEPTION 'internal error'; END IF; RETURN NEW; END; $$; 
- If I want to raise an error and stop execution (say in a
psqlscript with ON_ERROR_STOP) but still want the erroring record to be modified and inserted in the underlying table for traceability, how to do that? Raise error will remove the record, and if I return NEW only the execution continuesRafs– Rafs2024-10-24 14:00:16 +00:00Commented Oct 24, 2024 at 14:00