Databases
StackOverflow2013
Postsdbo
POa postgres update trigger performs everything else except the actual update
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POa postgres update trigger performs everything else except the actual update
    primarykey
    data
    text
    <p>Let's use a test table :</p> <pre><code>CREATE TABLE labs.date_test ( pkey int NOT NULL, val integer, date timestamp without time zone, CONSTRAINT date_test_pkey PRIMARY KEY (pkey) ); </code></pre> <p>I have a trigger function defined as below. It is a function to insert a date into a specified column in the table. Its arguments are the primary key, the name of the date field, and the date to be inserted:</p> <pre><code>CREATE OR REPLACE FUNCTION tf_set_date() RETURNS trigger AS $BODY$ DECLARE table_name text; pkey_col text := TG_ARGV[0]; date_col text := TG_ARGV[1]; date_val text := TG_ARGV[2]; BEGIN table_name := format('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); IF TG_NARGS != 3 THEN RAISE 'Wrong number of args for tf_set_date()' USING HINT='Check triggers for table ' || table_name; END IF; EXECUTE format('UPDATE %s SET %I = %s' || ' WHERE %I = ($1::text::%s).%I', table_name, date_col, date_val, pkey_col, table_name, pkey_col ) USING NEW; RAISE NOTICE '%', NEW; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE; </code></pre> <p>The actual trigger definition is as follows:</p> <pre><code>CREATE TRIGGER t_set_ready_date BEFORE UPDATE OF val ON labs.date_test FOR EACH ROW EXECUTE PROCEDURE tf_set_date('pkey', 'date', 'localtimestamp(0)'); </code></pre> <p>Now say I do: <code>INSERT INTO TABLE</code>date_test(pkey) values(1);`</p> <p>Then I perform an update as follows:</p> <pre><code>UPDATE labs.date_test SET val = 1 WHERE pkey = 1; </code></pre> <p>Now the date gets inserted as expected. But the <code>val</code> field is still <code>NULL</code>. It does not have <code>1</code> as one would expect (or rather as I expected).</p> <p>What am I doing wrong? The RAISE NOTICE in the trigger shows that NEW is still what I expect it to be. Aren't <code>UPDATE</code>s allowed in <code>BEFORE UPDATE</code> triggers? One <a href="https://stackoverflow.com/questions/15340990/postgres-update-statement-as-a-trigger#comment21678390_15342749">comment</a> about <a href="https://stackoverflow.com/questions/15340990/postgres-update-statement-as-a-trigger">postgres triggers</a> seems to indicate that original the UPDATE gets overwritten if there is an UPDATE statement in a BEFORE UPDATE trigger. Can someone help me out?</p> <h2>EDIT</h2> <p>I am trying to update the same table that invoked the trigger, and that too the same row which is to be modified by the UPDATE statement that invoked the trigger. I am running Postgresql 9.2</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USRichard
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POa postgres update trigger performs everything else except the actual update
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POa postgres update trigger performs everything else except the actual update
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't understand what you are trying to do? You don't need an update trigger for the `UPDATE` itself to work. If you want to change the value of some columns, simply assign them the value: e.g. `new.date_col := current_date`, not `update` clause needed
      singulars
      1. POa postgres update trigger performs everything else except the actual update
      1. USa_horse_with_no_name
    2. COGreat question, though it's always a good idea to mention your PostgreSQL version. (`select version()`). Good to see proper use of identifier quoting for once. You may find using the `regclass` pseudo-type more convenient than separate schema and table names, though.
      singulars
      1. POa postgres update trigger performs everything else except the actual update
      1. USCraig Ringer
    3. CO@CraigRinger I am using Postgresql 9.2. And thanks for the tip about `regclass`.
      singulars
      1. POa postgres update trigger performs everything else except the actual update
      1. USRichard
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload