Re: Problem with triggers

On 06/15/2010 02:01 PM, Sid wrote:
> Hi,
>
> I am writing trigger function for validating values inserted into table. The
> goal is to print user friendly messages when inserted value is wrong.
> When I check for null values everything works as expected:
> Inside trigger I have lines:
> if (new.tvalue is null) then
>              RAISE EXCEPTION 'error message';
> end if;
> This one works ok.
>
> But when I try to do something like that:
>
> if length(new.tvalue)>  20 then
>        RAISE EXCEPTION 'error message - too long';
> end if;
>
> then I get just information from database that given field value is to long
> (of course only when I try insert too long value).
>
> In fact even if I put RAISE EXCEPTION as first line inside trigger it does
> not work.
> All I get is ERROR: value to long......
>
>
>
> Trigger declaration looks as follows:
> CREATE TRIGGER bir_validate
>    BEFORE INSERT OR UPDATE
>    ON portal.documentation
>    FOR EACH ROW
>    EXECUTE PROCEDURE portal.dynamic_trigger_validate();
>
> Postgres 8.4.4 on Windows 7 32bit
>
> My question is: why do I get information about too long value before trigger
> fires?
> Can I change this behavior?
>
>

The database is beating you to the validation. Basically you are trying
to override the built in validation. To make this work you will need to
let the field be longer than you want i.e varchar with no length
argument and then let your trigger handle the validations.

--
Adrian Klaver
adrian.klaver@gmail.com