title: Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists
body: <p>I have a table <em>my_table</em> with columns <em>id</em> (int), <em>value</em> (varchar), and <em>modified</em> (timestamp). I want to update the timestamp by the trigger each time I set a new value. I'm changing thousands of rows in one query so I do not want to use the row-level trigger (aka FOR EACH ROW) and modify the whole updated set in one invocation and not the thousands for each row. This is the trigger I'm trying to create:</p>
  <pre><code>CREATE TRIGGER my_trigger
  AFTER UPDATE OF value
  ON my_schema.my_table
  REFERENCING NEW TABLE AS updated OLD TABLE AS prev
  FOR EACH STATEMENT
  EXECUTE PROCEDURE my_schema.my_trigger_function()
  </code></pre>
  <p>I'm getting an error during the creation:</p>
  <blockquote>
  <p>[0A000] ERROR: transition tables cannot be specified for triggers with column lists</p>
  </blockquote>
  <p>How can I access the updated data set if I remove the &quot;REFERENCING NEW TABLE AS updated OLD TABLE AS prev&quot; and won't be able to query and join on updated or prev transition tables? If I remove the &quot;OF value&quot; part I'd get the recursive trigger invocation since the trigger fires itself again by changing the same table but a different column. So the best solution I've come up with it's to eliminate the second recursive invocation by the IF condition:</p>
  <pre><code>CREATE TRIGGER my_trigger
  AFTER UPDATE
  ON my_schema.my_table
  REFERENCING NEW TABLE AS updated OLD TABLE AS prev
  FOR EACH STATEMENT
  EXECUTE PROCEDURE my_schema.my_trigger_function()
  
  CREATE OR REPLACE FUNCTION my_schema.my_trigger_function()
      RETURNS TRIGGER
      LANGUAGE PLPGSQL
  AS
  $$
  BEGIN
  
      IF EXISTS(SELECT 1
                 FROM updated
                          INNER JOIN prev ON updated.modified = prev.modified) THEN
          UPDATE my_schema.my_table
          SET modified = NOW()
          WHERE id IN (SELECT id FROM updated);
      END IF;
  
      RETURN NULL;
  END;
  $$;
  </code></pre>
  <p>If I knew how to access all updated rows with &quot;AFTER UPDATE OF value&quot; without <em>updated</em> and <em>prev</em> I'd make it better, that's why I'm here.</p>