Subject Re: [firebird-support] Why IN AUTONOMOUS TRANSACTION doesnt work here?
Author W O
Hello Sean

I like to do double validation: at language program level and at database level.

I don't like depending just of the language for any job, because always there are several programmers here, sometimes programming with different languages. If I can validate something in a stored procedure or trigger that is very good for me, so I can avoid than even dumb programmers do mistakes.

Of course it is very simple, at language program level, recording the errors happens but it would be more useful if the recording is doing in a stored procedure or trigger. So, I would not depending of the programmers for know what bad things happens.

But if it is impossible to record some errors, well ... it is impossible. And I should live with that.



On Fri, Sep 20, 2013 at 1:31 PM, Leyne, Sean <Sean@...> wrote:


> Because triggers could fix the data to be valid?

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

Unfortunately, the SQL standard and committee has not taken that view.

They expect that the developer/languages provide appropriate layers to ensure that database datatype sizing constraints are met/enforced before the data reaches the database.

> It seems logical to me for someone to want to
> create a BEFORE trigger that would trim varchar fields down to size, and

I would suggest that this is the worse place and approach to be enforcing the field lengths. The client/user is the correct location/actor to make the decision about how a string should be shortened.

Further, it would not take much effort to develop logic which performs the evaluation/validation before the data is sent, to save an extra back-and-forth with the server over the network.

> failing that, would let FB double-check the length after all the BEFORE triggers
> have had their way with the data.

To perform the length check twice would add needless CPU cycles for most operations.