Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Integrity Constraint

Re: Integrity Constraint

From: Stephan Born <stephan.born_at_beusen.de>
Date: Mon, 29 Nov 1999 09:19:47 +0100
Message-ID: <38423723.5181E55E@beusen.de>


> Hi,
>
> I need to check that a valid entry is being made to a field in a table.
> I am running Oracle 8.0.4
>
> The field is a varchar2(11) and the data should be exactly 10 characters
> long, the first 9 being numeric and the last character may be numeric or a
> character.
> Unfortunately, checking this at the client application is not an option.
> The checking needs to be done by the database.
>
> Could anyone give me an idea of how to achieve this please.
>

You have to use a trigger on that table which is called by insert and update (at least of this column)

try this:

create or replace trigger trg_on_table
before
insert or
update of <column you want to inspect: col> on <table you want to inspect: tab>
for each row
declare

    i integer;
    ch integer;
begin

    if length(:new.col) <> 10 then

        raise_apllication_error (-20000, 'It must to be 10 characters');     end if;

    for i in 1..9 loop

        ch := ascii(substr(:new.col, i, 1));
        if ch < 48 or ch > 57 then
            raise_application_error (-20000, 'The first 9 characters must be
numeric');
        end if;

    end loop;
end;

I didnt try this trigger, but it should show you the way to solve this problem

Let me know if this will help you, please.

Regards, Stephan
--


Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------


Received on Mon Nov 29 1999 - 02:19:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US