Hi.
CREATE OR REPLACE FUNCTION trigger_info()
RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
RAISE NOTICE 'trigger name: %: TG_OP: % WHEN: % TG_LEVEL: %',
TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
RAISE NOTICE ' old: %', OLD;
RAISE NOTICE ' old: %', NEW;
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
RETURN OLD;
END IF;
END;
$$;
drop table if exists ts;
create table ts(a int4range, b int);
insert into ts values('[1,10)', 2), ('[1,10)', 3);
CREATE TRIGGER ts_trig1
BEFORE INSERT OR UPDATE OR DELETE ON ts
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_info();
update ts for portion of a from 2 to 4 set b = 12;
The above UPDATE statement is triggering the BEFORE FOR EACH STATEMENT
action four times.
This appears to contradict the documentation mentioned below.
Am I missing something?
https://www.postgresql.org/docs/devel/sql-createtrigger.html
"""
In contrast, a trigger that is marked FOR EACH STATEMENT only executes
once for any given operation, regardless of how many rows it modifies
(in particular, an operation that modifies zero rows will still result
in the execution of any applicable FOR EACH STATEMENT triggers).
"""
--
jian
https://www.enterprisedb.com/
