---------- Original Message ----------- From: "Leyne, Sean" <s...@broadviewsoftware.com> > 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. -Philip
