Subject RE: [firebird-support] Why IN AUTONOMOUS TRANSACTION doesnt work here?
Author unordained
---------- Original Message -----------
From: "Leyne, Sean" <Sean@...>
> It is as expected, data type constraints are enforced before all other
> operations.
> What would be the purpose of firing a trigger if the data is not valid?
> Sean
------- End of Original Message -------

Because triggers could fix the data to be valid? We only enforce data-type
(including length limit on varchar) constraints preemptively. We don't enforce all
constraints, though -- NOT NULL, FK, PK, UQ constraints aren't checked until the
very end.

Triggers commonly "fix" data so it'll pass muster -- assigning sequence numbers in
not-null fields, filling in default values for some FK fields, stripping out
unwanted characters that a CHECK constraint would later balk at, etc. Inside a
BEFORE trigger, you can only safely assume that the NEW.* fields contain data that
matches the declared data-type (except for nullness), but you can't actually
assume that the data is already 'valid'. So I wouldn't say it's a blanket rule
that we don't fire triggers on invalid data. We only avoid firing triggers on data
that won't fit the destination datatypes.

And that can be confusing, because to modern programmers accustomed to nearly
unlimited string types, varchar(x) is more about CHECK(char_length(field) <= x)
[logical-layer] than about APIs and C compatibility [physical-layer]. It seems
logical to me for someone to want to create a BEFORE trigger that would trim
varchar fields down to size, and failing that, would let FB double-check the
length after all the BEFORE triggers have had their way with the data.