Thread: Trigger does not work as expected

Hi,

since 7.2 (I'm using 7.2.1) there is not anymore
an implicit truncation when I try to insert,update
a VARCHAR. (msg: value to long for type character
varying (XX))
Even though I could change these columns to TEXT,
there are reasons I don't want to.

So I tried the following with a TRIGGER:

(TABLE kunde, COLUMN kundennumm = varchar(20))

create or replace function laenge() returns opaque as '
begin
if length(new.kundennumm) > 6 then
        raise exception '' Error! To long.'';
        new.kundennumm = substr(new.kundennumm,1,6);
end if;
return new;
end
' Language 'plpgsql'

create trigger laenge_trig before insert or update on kunde
         for each row execute procedure laenge();

If I try to insert a value with 21 characters:

insert into kunde (kundennumm) values  ('123456789012345678901');

I get: value to long for type character varying (20)

I don't even see the exception. So the parser seems to checks the length
before the trigger? Did I misunderstand something?

Could somebody give me hint?

regards

Johann Zuschlag
zuschlag@online.de

On Wed, 27 Mar 2002 14:21:10 +0100, Johann Zuschlag wrote:

>create or replace function laenge() returns opaque as '
>begin
>if length(new.kundennumm) > 6 then
>        raise exception '' Error! To long.'';
          ^^^^^^^^
Sorry, skip this line! Can't do both.

But still, doesn't work.

regards

Johann Zuschlag
zuschlag@online.de