[GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger function is not called
Bill [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger function is not called
Tom Lane wrote: Bill [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger function is not called
Bill [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger function is not called
Tom Lane wrote: Bill [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger function is not called
Bill [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger function is not called
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
Re: [GENERAL] Trigger function is not called
Tom Lane wrote:
Bill [EMAIL PROTECTED] 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();
Re: [GENERAL] Trigger function is not called
Bill [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger function is not called
Tom Lane wrote:
Bill [EMAIL PROTECTED] 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();
Re: [GENERAL] Trigger function is not called
Bill [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger function is not called
Klint Gore [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger function is not called
Tom Lane wrote: Bill [EMAIL PROTECTED] 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
Re: [GENERAL] 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: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
