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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
