Re: Trigger function is not called

[Bill <pg@xxxxxxxxxx>]

Tom Lane wrote:

Bill <pg@xxxxxxxxxx> 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();