Re: Trigger function is not called

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