Bill wrote:
> The thing that has me confused is that the following table, trigger
> and trigger function work perfectly and the primary key for this table
> is also bigint not null. I added a bigint not null domain to this
> schema and changed the data type of the key to the domain and then I
> get the constraint violation. I changed the type of the key column
> back to bigint not null and the trigger fires and no error occurs.
Perhaps explain verbose on the insert will make things clearer. When
the domain is used, there's a COERCETODOMAIN step that gets the constant
into the domain type. With the not null definition in the domain, this
blows up before anything else has a chance.
begin;
create schema test;
create sequence test.id_seq;
create domain mydom as bigint not null;
CREATE TABLE test.trigger_test
(
"key" bigint NOT NULL,
data character varying(16),
CONSTRAINT trigger_test_key PRIMARY KEY (key)
);
CREATE TABLE test.trigger_test2
(
"key" mydom,
data character varying(16),
CONSTRAINT trigger_test_key2 PRIMARY KEY (key)
);
CREATE OR REPLACE FUNCTION test.trigger_test_before_insert()
RETURNS trigger AS
$BODY$
begin
raise notice '*****Test before insert*****';
new."key" := nextval('test.id_seq');
return new;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trigger_test_insert
BEFORE INSERT
ON test.trigger_test
FOR EACH ROW
EXECUTE PROCEDURE test.trigger_test_before_insert();
CREATE TRIGGER trigger_test_insert2
BEFORE INSERT
ON test.trigger_test2
FOR EACH ROW
EXECUTE PROCEDURE test.trigger_test_before_insert();
explain verbose insert into test.trigger_test values (null,'hi');
--explain verbose insert into test.trigger_test2 values (null,'hi');
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
