Weird Trigger behaviour
От | Glen Eustace |
---|---|
Тема | Weird Trigger behaviour |
Дата | |
Msg-id | 1032555562.22255.88.camel@agree-6 обсуждение исходный текст |
Ответы |
Re: Weird Trigger behaviour
|
Список | pgsql-general |
Hi Jeff, Here is the letter I sent to bugs. ------------------------ Version 7.2.1 I have a small table with a unique primary key and a second history table that inherits the first and add a modification tstamp. If I attempt to insert a duplicate it is not permitted by psql as expected. I have a trigger on the table to write the OLD record to the history table. I have tried both before and after, it doesn't change the behaviour. When I update a row in the table, I get a duplicate created. i.e. the update does an insert which allows the duplicate key. I also get the correct record in the history table. drop table "vlan_hist"; drop table "vlan"; create table "vlan" ( "id" integer, "name" text, "site" text, "network" inet, "gateway" inet, primary key( "id" ) ); create table "vlan_hist" ( "mod_tstamp" timestamp ) inherits( "vlan" ); drop function "vlan_mod" (); create function "vlan_mod" ( ) returns opaque as ' begin insert into "vlan_hist" values ( OLD.id, OLD.name, OLD.site, OLD.network, OLD.gateway, ''now()'' ); return NULL; end; ' language 'plpgsql'; drop trigger "vlan_mod_trigger" on "vlan"; create trigger "vlan_mod_trigger" after UPDATE on "vlan" for each row execute procedure "vlan_mod" (); insert into vlan values ( 1, 'Here','TUR','130.123.104.0/22','130.123.104.200' ); netdbase=> update vlan set name='No Ok' where id=1; UPDATE 1 netdbase=> select * from vlan; id | name | site | network | gateway ----+-------+------+------------------+----------------- 1 | No Ok | TUR | 130.123.104.0/22 | 130.123.104.200 1 | Here | TUR | 130.123.104.0/22 | 130.123.104.200 (2 rows) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
В списке pgsql-general по дате отправления: