The trigger with `BEFORE` or `AFTER` and `INSERT`, `UPDATE` or `DELETE` on a view doesn't work in PostgreSQL

Question

I created, as shown below:

• A table called person
• A table called log, with 1 row inserted in it.
• A view called my_v
• A trigger function called my_func(), whose purpose is to increment num.log by 1.
• A trigger called my_t, that executes my_func() before each statement (INSERT, UPDATE or DELETE) on my_v.

CREATE TABLE person (
  name VARCHAR(20)
);

CREATE TABLE log (
  num INTEGER
);
INSERT INTO log (num) VALUES (0);

CREATE VIEW my_v AS
  SELECT * FROM person;

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  UPDATE log SET num = num + 1;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_t BEFORE INSERT OR UPDATE OR DELETE ON my_v
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

For good measure, I also tested when the trigger my_t executes after each statement

CREATE TRIGGER my_t AFTER INSERT OR UPDATE OR DELETE ON my_v
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

With this setup, the value of num in table log is expected to count how many times my_func() was invoked by the trigger.

When inserting, updating or deleting a row into/from my_v, log.num remains at 0. See below the statements, the last one of which is the check from table log (actually tested after each action but removed from here in order to shorten the question):

postgres=# INSERT INTO my_v (name) VALUES ('John');
INSERT 0 1 /* -> 1 row successfully inserted */

postgres=# UPDATE my_v SET name = 'Tom';
UPDATE 1 /* -> 1 row successfully updated */

postgres=# DELETE FROM my_v;
DELETE 1 /* -> 1 row successfully deleted */

postgres=# SELECT num FROM log;
 num
-----
   0
(1 row)

So, the trigger with BEFORE or AFTER and INSERT, UPDATE or DELETE on a view doesn't work in PostgreSQL.

Important: an alternative version of the my_t trigger with INSTEAD OF and FOR EACH ROW does work properly:

CREATE TRIGGER my_t INSTEAD OF INSERT OR UPDATE OR DELETE ON my_v
FOR EACH ROW EXECUTE FUNCTION my_func();

How can I create my_t trigger which triggers my_func() before or after INSERT, UPDATE or DELETE operation happens on my_v for each statement?

Answer 1

This is by design, although not obvious it is documented. BEFORE / AFTER triggers on Views is only supported for those views that have an INSTEAD OF trigger.

CREATE TRIGGER
(one of the final paragraphs before Examples)
Statement-level triggers on a view are fired only if the action on the view is handled by a row-level INSTEAD OF trigger. If the action is handled by an INSTEAD rule, then whatever statements are emitted by the rule are executed in place of the original statement naming the view, so that the triggers that will be fired are those on tables named in the replacement statements. Similarly, if the view is automatically updatable, then the action is handled by automatically rewriting the statement into an action on the view's base table, so that the base table's statement-level triggers are the ones that are fired.

Kudos to @Damien_The_Unbeliever for identifying this reference

We can demonstrate this working in a fiddle: https://www.db-fiddle.com/f/Ki2fBnAKC4rH1kMs66UaT/0

CREATE TABLE person (
  name VARCHAR(20)
);

CREATE TABLE log (
  BeforeViewCounter INTEGER,
  InsteadOfViewCounter INTEGER,
  BeforeTableCounter INTEGER
);
INSERT INTO log (BeforeViewCounter, InsteadOfViewCounter, BeforeTableCounter) VALUES (0,0,0);

CREATE VIEW my_v AS
  SELECT * FROM person;

CREATE FUNCTION beforeView_func() RETURNS trigger AS 
'
BEGIN
  UPDATE log SET BeforeViewCounter = BeforeViewCounter + 1;
  RETURN NULL;
END;
' 
LANGUAGE plpgsql;
CREATE FUNCTION insteadView_func() RETURNS trigger AS 
'
BEGIN
  UPDATE log SET InsteadOfViewCounter= InsteadOfViewCounter + 1;
  
  IF (TG_OP = ''DELETE'') THEN
    DELETE FROM person WHERE name = OLD.name; 
    IF NOT FOUND THEN RETURN NULL; END IF;
    RETURN OLD;
  ELSIF (TG_OP = ''UPDATE'') THEN
    UPDATE person SET name = NEW.name WHERE name = OLD.name;
    RETURN NEW;
  ELSIF (TG_OP = ''INSERT'') THEN
    INSERT INTO person SELECT NEW.*;
    RETURN NEW;
  END IF;
  RETURN NULL; 
END;
' 
LANGUAGE plpgsql;
CREATE FUNCTION beforeTable_func() RETURNS trigger AS 
'
BEGIN
  UPDATE log SET BeforeTableCounter= BeforeTableCounter + 1;
  RETURN NEW;
END;
' 
LANGUAGE plpgsql;

CREATE TRIGGER before_t BEFORE INSERT OR UPDATE OR DELETE ON my_v
FOR EACH STATEMENT EXECUTE FUNCTION beforeView_func();

CREATE TRIGGER beforeTable_t BEFORE INSERT OR UPDATE OR DELETE ON person
FOR EACH ROW EXECUTE FUNCTION beforeTable_func();


CREATE TRIGGER intead_t INSTEAD OF INSERT OR UPDATE OR DELETE ON my_v
FOR EACH ROW EXECUTE FUNCTION insteadView_func();



INSERT INTO my_v (name) VALUES ('John');
SELECT * FROM log FULL OUTER JOIN my_v ON 1=1;
UPDATE my_v SET name = 'Tom';
SELECT * FROM log FULL OUTER JOIN my_v ON 1=1;
DELETE FROM my_v;
SELECT * FROM log FULL OUTER JOIN my_v ON 1=1;

There is a flaw in the delete handler, name should be null in the end but its not important to this demo.

beforeviewcounter	insteadofviewcounter	beforetablecounter	name
3	3	3	Tom

If you run the same test, this time with the INSTEAD OF trigger removed https://www.db-fiddle.com/f/jE6tbJAGTGRqpYKgXW346n/1 then only the trigger on the table fires

beforeviewcounter	insteadofviewcounter	beforetablecounter	name
0	0	3	null

Although this looks like a strange implementation, the purpose behind this is to extend the support for INSERT/UPDATE/DELETE against complex views that can only be defined in INSTEAD OF triggers. The subsystem will call a statement level BEFORE trigger once, before executing the INSTEAD OF trigger for each ROW. At the end of the operation the AFTER statement trigger will be called.

Other RDBMS do not generally support triggers on views. This was not designed to allow BEFORE/AFTER on any arbitrary view but specifically to execute pre-conditions that can prevent the INSTEAD OF ROW statements from executing at all but also because the order of the ROW operations is indeterminate so it allows an extensibility site to assist you to audit or trace the entire batch instead of you trying to engineer solutions that try to track the difference between first/last ROW inside the FOR EACH ROW.