Thread: Precedence of a TRIGGER vs. a CHECK on a column

Hi,

I've got a table:

<code language="SQL">
CREATE TABLE "common"."dynamic_enum"
  (
        "pk_id"         integer                         DEFAULT
nextval('"common"."pw_seq"')
    ,   "enum_type"     common.non_empty_name
    ,   "value"         integer             NOT NULL    DEFAULT
nextval('"common"."de_local_seq"')
    ,   "name"          common.not_all_digits
    ,   "display_name"  varchar(256)
    ,   "description"   varchar(4000)
    ,   "sort_order"    common.sort_order_type
    ,   "is_internal"   boolean             NOT NULL    DEFAULT false
    ,   LIKE "common"."usage_tracking_columns" INCLUDING DEFAULTS
  )
  WITHOUT OIDS
  ;
</code>

Where common.non_empty_name is defined as:

<code language="SQL">
CREATE DOMAIN common.non_empty_name AS varchar(256) NOT NULL
     CONSTRAINT Not_Empty CHECK ( VALUE<>'' );
</code>

I'm using COPY to load some data and I want to set the "enum_type" which is
not present in the file which contains the to-be-loaded data.  So, I define
a trigger:

<code language="PL/pgSQL">
CREATE OR REPLACE FUNCTION "merchandise".trg_insert_de_temp()
    RETURNS trigger AS '
BEGIN
    IF ( NEW."enum_type" IS NULL) THEN
        NEW."enum_type" =''group_code'';
    END IF;
    RETURN NEW;
END;
  ' LANGUAGE plpgsql VOLATILE;
</code>
<code language="SQL">
CREATE TRIGGER zz_set_enum_type_temp BEFORE INSERT ON
"common"."dynamic_enum"
    FOR EACH ROW EXECUTE PROCEDURE "merchandise".trg_insert_de_temp();

</code>

But, when I do the COPY I get:

<snip type="psql-output">
psql:load_yurman_merchandise.de.sql:59: ERROR:  domain non_empty_name does
not allow null values
CONTEXT:  COPY dynamic_enum, line 1: "BRACELET  Bracelet"
</snip>

So it seems that the CHECK definied for the non_empty_name domain is being
applied before the trigger is executed.  Yet, it seems that NON NULL
constraints are applied after triggers get called.

Questions:
    1.  Is the just-described ordering accurate?
    2.  Is that intended (e.g., the way it "should" be because of, say, SQL
standard)
    3.  Is there a work-around (short of changing the definition for the
relevant column)?

Thanks,

== Ezra Epstein

On Sun, 11 Jan 2004, ezra epstein wrote:
> So it seems that the CHECK definied for the non_empty_name domain is being
> applied before the trigger is executed.  Yet, it seems that NON NULL
> constraints are applied after triggers get called.

I think it's that your domain constraint is being applied before the
trigger is executed and that the table constraints are being applied
after given that I get the same behavior with a domain constraint of not
null.  This makes sense (although I haven't checking the spec wording)
since the value is being coerced into the domain in order to be put into
the row that's being passed to the trigger (thus triggering the domain
constraints).

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I think it's that your domain constraint is being applied before the
> trigger is executed and that the table constraints are being applied
> after given that I get the same behavior with a domain constraint of not
> null.  This makes sense (although I haven't checking the spec wording)
> since the value is being coerced into the domain in order to be put into
> the row that's being passed to the trigger (thus triggering the domain
> constraints).

IIRC other datatype-related constraints, such as max length for a
char(n) or varchar(n) column, are also checked before triggers are
fired.  We have had complaints about that before, mainly from people
who wanted to use a trigger to truncate a varchar value before the
constraint gets checked.

I think this ordering of operations is largely an implementation
artifact and could in theory be changed, but I'm disinclined to change
it unless someone can show that the spec requires different behavior.
In particular, ISTM that if we change it, the input to the trigger
wouldn't really be a legal value of the table's rowtype.

            regards, tom lane