Precedence of a TRIGGER vs. a CHECK on a column

[ezra epstein]

Hi,

I've got a table:

<code language="SQL">
CREATE TABLE "common"."dynam ic_enum"
(
"pk_id" integer DEFAULT
nextval('"commo n"."pw_seq"' )
, "enum_type" common.non_empt y_name
, "value" integer NOT NULL DEFAULT
nextval('"commo n"."de_local_se q"')
, "name" common.not_all_ digits
, "display_na me" varchar(256)
, "descriptio n" varchar(4000)
, "sort_order " common.sort_ord er_type
, "is_interna l" boolean NOT NULL DEFAULT false
, LIKE "common"."usage _tracking_colum ns" INCLUDING DEFAULTS
)
WITHOUT OIDS
;
</code>

Where common.non_empt y_name is defined as:

<code language="SQL">
CREATE DOMAIN common.non_empt y_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".t rg_insert_de_te mp()
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_typ e_temp BEFORE INSERT ON
"common"."dynam ic_enum"
FOR EACH ROW EXECUTE PROCEDURE "merchandise".t rg_insert_de_te mp();

</code>

But, when I do the COPY I get:

<snip type="psql-output">
psql:load_yurma n_merchandise.d e.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

[Stephan Szabo]

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).

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

Stephan Szabo <ss****@megazon e.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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings