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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Bill <pg@dbginc.com> writes:
> 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.

The script you show attempts to create the trigger before creating the
function, which of course isn't going to work. Did you check whether
the trigger actually got created?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> Bill <pg@dbginc.com> writes:
>
>> 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.
>>
>
> The script you show attempts to create the trigger before creating the
> function, which of course isn't going to work. Did you check whether
> the trigger actually got created?
>
> regards, tom lane
>
>
>
The trigger was definitely created. The code I posted was not a script
that I used to create the trigger and trigger function. I just copied
the SQL from pgAdmin and pasted the commands into my message not paying
any attention to the order. Sorry for the confusion.

In a newsgroup posting someone suggested that constraint checks on
domains occur before the before insert trigger. That seems difficult to
believe based on my experience with other databases. Do constraint
checks on domains occur before the before insert trigger?

Bill

Bill <pg@dbginc.com> writes:
> In a newsgroup posting someone suggested that constraint checks on
> domains occur before the before insert trigger.

Yeah, that is the case, but if a domain check was failing then the
row wouldn't get inserted, so I'm not clear on how this matches up
with your report.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> Bill <pg@dbginc.com> writes:
>
>> In a newsgroup posting someone suggested that constraint checks on
>> domains occur before the before insert trigger.
>>
>
> Yeah, that is the case, but if a domain check was failing then the
> row wouldn't get inserted, so I'm not clear on how this matches up
> with your report.
>
> regards, tom lane
>
>
>
The row is not getting inserted. I just created a test table and trigger
and confirmed that the trigger fires if the column is defined as bigint
not null and fails after I change the type to the domain. I will alter
all of the tables and get rid of the domain.

Is it possible to create a type and use that instead of the domain or
will I have the same problem with a type?

Bill

You'd have the same problem. By the time the trigger sees it, the row
> has already been converted to the table's column datatype(s), so any
> exception associated with a datatype or domain would be thrown already.
>
> A lot of people seem to have trouble with this concept; I dunno what
> data representation they think the trigger is working on...
>
> If you want to enforce constraints for a table in the trigger, you can
> do that, but it's not going to work to try to mix and match
> trigger-based and datatype-based restrictions.
>
> regards, tom lane
>
>
>
I have no problem with the concept now that I understand it. It is just
different than InterBase and Firebird which I have done a lot of work
with lately. Thanks very much for your help.

Bill

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> Bill <pg@dbginc.com> writes:
>
>> Is it possible to create a type and use that instead of the domain or
>> will I have the same problem with a type?
>>
>
> You'd have the same problem. By the time the trigger sees it, the row
> has already been converted to the table's column datatype(s), so any
> exception associated with a datatype or domain would be thrown already.
>
> A lot of people seem to have trouble with this concept; I dunno what
> data representation they think the trigger is working on...
>
> If you want to enforce constraints for a table in the trigger, you can
> do that, but it's not going to work to try to mix and match
> trigger-based and datatype-based restrictions.
>
> regards, tom lane
>
>
>
I removed the domain from the category_id and version columns leaving
the following table, trigger function and trigger. The trigger function
is still not called when I insert a new row. Any other ideas?

Bill

CREATE TABLE note.category
(
category_id bigint NOT NULL,
category character varying(40) NOT NULL,
uc_category note.d_category,
parent_category_id bigint,
"version" bigint NOT NULL,
category_checked boolean NOT NULL DEFAULT false,
CONSTRAINT category_primary_key PRIMARY KEY (category_id)
)

CREATE OR REPLACE FUNCTION note.category_bi()
RETURNS trigger AS
$BODY$
BEGIN
RAISE NOTICE '******CATEGORY BI******';
IF (NEW.CATEGORY IS NULL OR NEW.CATEGORY = '') THEN
RAISE EXCEPTION 'Category cannot be blank.';
END IF;

IF (NEW.CATEGORY_ID IS NULL) THEN
NEW.CATEGORY_ID := nextval('note.id_seq');
END IF;

NEW.VERSION := nextval('note.version_seq');
NEW.UC_CATEGORY := UPPER(NEW.CATEGORY);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE


CREATE TRIGGER category_bi_trigger
BEFORE UPDATE
ON note.category
FOR EACH ROW
EXECUTE PROCEDURE note.category_bi();

Bill <pg@dbginc.com> writes:
> I removed the domain from the category_id and version columns leaving
> the following table, trigger function and trigger. The trigger function
> is still not called when I insert a new row. Any other ideas?

You're still expecting the trigger to get invoked before any constraints
are enforced (the NOT NULLs being the problem here, I think). Again,
you can enforce things through a trigger or through a table constraint,
but mixing and matching won't work too well.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> Bill <pg@dbginc.com> writes:
>
>> I removed the domain from the category_id and version columns leaving
>> the following table, trigger function and trigger. The trigger function
>> is still not called when I insert a new row. Any other ideas?
>>
>
> You're still expecting the trigger to get invoked before any constraints
> are enforced (the NOT NULLs being the problem here, I think). Again,
> you can enforce things through a trigger or through a table constraint,
> but mixing and matching won't work too well.
>
> regards, tom lane
>
>
>
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.

Bill

CREATE TABLE test.trigger_test
(
"key" bigint NOT NULL,
data character varying(16),
CONSTRAINT trigger_test_key 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();

Tom Lane wrote:
> Bill <pg@dbginc.com> writes:
>
>> 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.
>>
>
> Actually, after looking closer, I think the problem with your previous
> example is that you created an ON UPDATE trigger not an ON INSERT
> trigger. Table constraints are indeed enforced after before-triggers
> fire, as a quick look at the code proves. Sorry for the misinformation.
>
> regards, tom lane
>
>
>
I knew I was missing something really simple. I changed the trigger to
before insert and everything works perfectly. Thanks again for your
help. I learned a lot.

Bill