Mark

Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being "prepared" by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it "non-compliant" so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere.
Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found.

If you think it breaking the problem by level, it is easier to see the issue, let's think it this way:
1) Level Requester, where the insert statement is created, or App.
2) Level Massaging where the triggers occur before being ready to pass to the data saving process.
3) Level Archiving, where the DB validates and either saves or returns error to requester.

The key is understanding that in 1) and 2) there is still "change occurring" while 3) is a black or white outcome, where either the request can be inserted or it is "illegal" and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it.

Cheers
Fabian

--- In firebird-support@yahoogroups.com, Mark Rotteveel wrote:
>
> On Wed, 16 Jan 2013 04:36:36 -0000, "fabianchocron"
> wrote:
> > Hi All,
> >
> > We are trying to resolve an issue caused by the Application inserting a
> > string larger than the database field size. The planned solution was to
> add
> > a trigger before insert as follows:
> > new.string = substring(new.string from 1 for 100);
> >
> > We did not modify the application because we don't have at this stage
> the
> > ability to recompile it without a massive effort.
> >
> > For some reason that we do not understand the trigger is not working as
> > desired, and the insert fails with an arithmetic overflow or string
> > truncation error. Is is possible that Firebird 2.1 64 bits has a bug and
> we
> > may need to upgrade to resolve this issue? Or are we doing something
> wrong?
>
> This is intentional behaviour (I believe it is even specified in the SQL
> specification, but not 100% sure on that). If you submit data longer than
> can be stored, it will result in an exception because otherwise it would
> result in loss of data. Only the client program would know how to deal with
> this. A workaround would be to use an updatable view and handle the
> transformation in the view trigger, however you can still in run into
> problems when you exceed the maximum CHAR or VARCHAR lengths.
>
> Mark
>