[GENERAL] After Insert or Update Trigger Issues!

[Kyrill Alyoshin]

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

<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>