Trigger function is not called
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
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
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
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
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
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
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
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();
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
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
Klint Gore <kgore4@une.edu.au> writes:
... With the not null definition in the domain, this
blows up before anything else has a chance.
Right. Forming the proposed row-to-insert involves coercing the data to
the correct data types, and for domain types enforcing the domain
constraints is seen as part of that. So you can't use a trigger to
clean up problems that violate the column's datatype definition.
However, constraints associated with the *table* (such as a NOT NULL
column constraint in the table definition) are enforced only after the
before-trigger(s) fire. So you could use a table constraint to backstop
something you're expecting a trigger to enforce.
This difference is probably what's confusing Bill, and I didn't help any
by giving wrong information about it just now. Sorry again.
regards, tom lane
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