After Insert or Update Trigger Issues! - Mailing list pgsql-general

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