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
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
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
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
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: > 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: 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
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
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
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: > 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: 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: > 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
[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