Thread: After Insert or Update Trigger Issues!

Hi guys,


I cannot get AFTER INSERT (or UPDATE for that matter) triggers to
work. The same code works perfectly fine for BEFORE triggers.


I am almost ready to think that this is a bug. Just want to run it by
you, guys. OK, here it is:



1. MY FUNCTIONS


<fixed><x-tad-bigger>CREATE OR REPLACE FUNCTION insert_stamp() RETURNS
TRIGGER AS $audit_insert$

    BEGIN

        NEW.created_ts := 'now';

        NEW.updated_ts := 'now';

        RETURN NEW;

    END;

$audit_insert$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION update_stamp() RETURNS TRIGGER AS
$audit_update$

    BEGIN

        NEW.updated_ts := 'now';

        RETURN NEW;

    END;

$audit_update$ LANGUAGE plpgsql;



2. MY TABLE


CREATE TABLE country (

    country_id            INT4    PRIMARY KEY,

    country_name        TEXT    NOT NULL    CONSTRAINT country_name_uq UNIQUE,

    country_iso_name    TEXT    NOT NULL    CONSTRAINT country_iso_name_uq UNIQUE,

    list_rank            INT4    NOT NULL     CONSTRAINT country_list_rank_uq UNIQUE,

    version                INT4    NOT NULL,

    created_ts            TIMESTAMP,

    updated_ts            TIMESTAMP

);


CREATE UNIQUE INDEX upper_country_name_idx ON country
(UPPER(country_name));

CREATE UNIQUE INDEX upper_country_iso_name_idx ON country
(UPPER(country_iso_name));


CREATE TRIGGER insert_stamp AFTER INSERT ON country

    FOR EACH ROW EXECUTE PROCEDURE insert_stamp();



CREATE TRIGGER update_stamp AFTER UPDATE ON country

    FOR EACH ROW EXECUTE PROCEDURE update_stamp();



(Please keep in mind that if I switch to BEFORE (vs.AFTER) -
EVERYTHING WORKS!!!)



3. MY RESULTS


hibertest=# \d country

                   Table "public.country"

      Column      |            Type             | Modifiers

------------------+-----------------------------+-----------

 country_id       | integer                     | not null

 country_name     | text                        | not null

 country_iso_name | text                        | not null

 list_rank        | integer                     | not null

 version          | integer                     | not null

 created_ts       | timestamp without time zone |

 updated_ts       | timestamp without time zone |

Indexes:

    "country_pkey" PRIMARY KEY, btree (country_id)

    "country_iso_name_uq" UNIQUE, btree (country_iso_name)

    "country_list_rank_uq" UNIQUE, btree (list_rank)

    "country_name_uq" UNIQUE, btree (country_name)

    "upper_country_iso_name_idx" UNIQUE, btree
(upper(country_iso_name))

    "upper_country_name_idx" UNIQUE, btree (upper(country_name))

Triggers:

    insert_stamp AFTER INSERT ON country FOR EACH ROW EXECUTE
PROCEDURE insert_stamp()

    update_stamp AFTER UPDATE ON country FOR EACH ROW EXECUTE
PROCEDURE update_stamp()


hibertest=# insert into country values (10, 'USA', 'US', 1, 1);

INSERT 3538132 1

hibertest=# select * from country;

 country_id | country_name | country_iso_name | list_rank | version |
created_ts | updated_ts

------------+--------------+------------------+-----------+---------+------------+------------

         10 | USA          | US               |         1 |       1 |
|

(1 row)





Does anyone know why this may be the case???


Thank you very much!


Kyrill Alyoshin





</x-tad-bigger></fixed>

Hi guys,

I cannot get AFTER INSERT (or UPDATE for that matter) triggers to work.
The same code works perfectly fine for BEFORE triggers.

I am almost ready to think that this is a bug. Just want to run it by
you, guys. OK, here it is:


1. MY FUNCTIONS

CREATE OR REPLACE FUNCTION insert_stamp() RETURNS TRIGGER AS
$audit_insert$
    BEGIN
        NEW.created_ts := 'now';
        NEW.updated_ts := 'now';
        RETURN NEW;
    END;
$audit_insert$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_stamp() RETURNS TRIGGER AS
$audit_update$
    BEGIN
        NEW.updated_ts := 'now';
        RETURN NEW;
    END;
$audit_update$ LANGUAGE plpgsql;


2. MY TABLE

CREATE TABLE country (
    country_id            INT4    PRIMARY KEY,
    country_name        TEXT    NOT NULL    CONSTRAINT country_name_uq UNIQUE,
    country_iso_name    TEXT    NOT NULL    CONSTRAINT country_iso_name_uq UNIQUE,
    list_rank            INT4    NOT NULL     CONSTRAINT country_list_rank_uq UNIQUE,
    version                INT4    NOT NULL,
    created_ts            TIMESTAMP,
    updated_ts            TIMESTAMP
);

CREATE UNIQUE INDEX upper_country_name_idx ON country
(UPPER(country_name));
CREATE UNIQUE INDEX upper_country_iso_name_idx ON country
(UPPER(country_iso_name));

CREATE TRIGGER insert_stamp AFTER INSERT ON country
    FOR EACH ROW EXECUTE PROCEDURE insert_stamp();

CREATE TRIGGER update_stamp AFTER UPDATE ON country
    FOR EACH ROW EXECUTE PROCEDURE update_stamp();


(Please keep in mind that if I switch to BEFORE (vs.AFTER) - EVERYTHING
WORKS!!!)


3. MY RESULTS

hibertest=# \d country
                    Table "public.country"
       Column      |            Type             | Modifiers
------------------+-----------------------------+-----------
  country_id       | integer                     | not null
  country_name     | text                        | not null
  country_iso_name | text                        | not null
  list_rank        | integer                     | not null
  version          | integer                     | not null
  created_ts       | timestamp without time zone |
  updated_ts       | timestamp without time zone |
Indexes:
     "country_pkey" PRIMARY KEY, btree (country_id)
     "country_iso_name_uq" UNIQUE, btree (country_iso_name)
     "country_list_rank_uq" UNIQUE, btree (list_rank)
     "country_name_uq" UNIQUE, btree (country_name)
     "upper_country_iso_name_idx" UNIQUE, btree (upper(country_iso_name))
     "upper_country_name_idx" UNIQUE, btree (upper(country_name))
Triggers:
     insert_stamp AFTER INSERT ON country FOR EACH ROW EXECUTE PROCEDURE
insert_stamp()
     update_stamp AFTER UPDATE ON country FOR EACH ROW EXECUTE PROCEDURE
update_stamp()

hibertest=# insert into country values (10, 'USA', 'US', 1, 1);
INSERT 3538132 1
hibertest=# select * from country;
  country_id | country_name | country_iso_name | list_rank | version |
created_ts | updated_ts
------------+--------------+------------------+-----------+---------
+------------+------------
          10 | USA          | US               |         1 |       1 |
          |
(1 row)



Does anyone know why this may be the case???

Thank you very much!

Kyrill Alyoshin

Kyrill Alyoshin <kyrill@technolog.ca> writes:
> 1. MY FUNCTIONS

> CREATE OR REPLACE FUNCTION insert_stamp() RETURNS TRIGGER AS
> $audit_insert$
>     BEGIN
>         NEW.created_ts := 'now';
>         NEW.updated_ts := 'now';
>         RETURN NEW;
>     END;
> $audit_insert$ LANGUAGE plpgsql;

Do you understand the difference between a BEFORE trigger and an AFTER
trigger?  An AFTER trigger fires *after* the operation is done.
Therefore it can't affect the data that was stored.  It's no surprise
that the above is a no-op when used as an AFTER trigger; it's just
modifying a row in memory that will be thrown away afterwards.

Usually AFTER triggers are used to propagate data to other tables;
in that scenario, what you want is precisely to know what the final
state of the row is, after all the BEFORE triggers got done doing their
things.

            regards, tom lane

On Sun, Mar 27, 2005 at 07:28:21PM -0500, Kyrill Alyoshin wrote:
>
> I cannot get AFTER INSERT (or UPDATE for that matter) triggers to work.
> The same code works perfectly fine for BEFORE triggers.

You're trying to modify the record but it's too late in an AFTER
trigger.  See the "Triggers" chapter in the documentation:

  The return value is ignored for row-level triggers fired after
  an operation, and so they may as well return NULL.

The PL/pgSQL "Trigger Procedures" section repeats the above:

  The return value of a BEFORE or AFTER statement-level trigger
  or an AFTER row-level trigger is always ignored; it may as well
  be null.

http://www.postgresql.org/docs/8.0/interactive/triggers.html
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/