PostgreSQL 8.3 on Windows. I have the table below which has a before
insert trigger. The CREATE TRIGGER statement and the trigger function
are also shown below. When I insert a row into this table using pgAdmin
III and the INSERT statement
insert into note.category (category_id, category)
values(689, 'Ztest');
the before insert trigger function is not called. The notice is not
displayed and no value is assigned to the version or uc_category columns
and the insert fails with a violation of the not null constraint on the
version field? I have created a simple two column test table with a
before insert trigger and it works perfectly. I am new to PostgreSQL so
I suspect I am missing something simple but I cannot figure out what.
Why is the trigger function never called?
Thanks,
Bill
CREATE TABLE note.category
(
category_id note.d_id NOT NULL,
category note.d_category NOT NULL,
uc_category note.d_category,
parent_category_id note.d_id_fk,
"version" note.d_id,
category_checked boolean NOT NULL DEFAULT false,
CONSTRAINT category_primary_key PRIMARY KEY (category_id)
)
WITH (OIDS=FALSE);
ALTER TABLE note.category OWNER TO postgres;
CREATE TRIGGER category_bi_trigger
BEFORE INSERT
ON note.category
FOR EACH ROW
EXECUTE PROCEDURE note.category_bi();
CREATE OR REPLACE FUNCTION note.category_bi()
RETURNS trigger AS
$BODY$
begin
RAISE NOTICE '*****CATEGORY BEFORE INSERT*****';
NEW.VERSION := nextval('note.version_seq');
NEW.UC_CATEGORY := UPPER(NEW.CATEGORY);
RETURN NEW;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
