469,648 Members | 1,195 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

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

Nov 12 '05 #1
2 2468

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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2
Stephan Szabo <ss****@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

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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Alejandro | last post: by
2 posts views Thread by Trevor Fairchild | last post: by
8 posts views Thread by Stuart McGraw | last post: by
reply views Thread by JohnO | last post: by
5 posts views Thread by Bob Stearns | last post: by
3 posts views Thread by beer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.