sexta-feira, maio 12, 2006
NC Postgresql trigger
test=> CREATE FUNCTION trigger_insert_update_statename()
test-> RETURNS opaque
test-> AS 'BEGIN
test'> IF new.code !~ ''^[A-Za-z][A-Za-z]$''
test'> THEN RAISE EXCEPTION ''State code must be two alphabetic characters.'';
test'> END IF;
test'> IF new.name !~ ''^[A-Za-z ]*$''
test'> THEN RAISE EXCEPTION ''State name must be only alphabetic characters.'';
test'> END IF;
test'> IF length(trim(new.name)) < 3
test'> THEN RAISE EXCEPTION ''State name must longer than two characters.'';
test'> END IF;
test'> new.code = upper(new.code); -- uppercase statename.code
test'> new.name = initcap(new.name); -- capitalize statename.name
test'> RETURN new;
test'> END;'
test-> LANGUAGE 'plpgsql';
