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

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

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