limit column char trigger plsql

I’m starting to learn PLSQL and created a very very simple table, I’m trying to get familiar with functions and triggers.

My table:

create table customer(
f_name varchar(30) not null,
s_name varchar(30) not null,
passwd varchar(20) not null,
constraint customer_f_name_pk primary key (f_name));

Now to the question, I want to limit before insert or update new row (biuefer) so that the first name must be 8 characters, not less, not more. If you try to insert a row with a name less or more than 8 character I want a message printed out.

create or replace trigger biufer_customer
before insert or update
of f_name
on customer
for each row
begin
    *NO IDEA WHAT TO TYPE*
    raise_application_error(-20001,'So I type my text here');
end;

Answer

If you wanted to implement such constraint, you would typically use a check constraint rather than a trigger:

create table customer(
    f_name varchar2(8 char) not null check(length(f_name) = 8))
    s_name varchar2(30 char) not null,
    passwd varchar2(20 char) not null,
    constraint customer_f_name_pk primary key (f_name)
);

Notes:

• that there is no point declaring a varchar(30) if you don’t allow more than 8 characters anyway, so I shrinked that

• you want varchar2 rather than varchar (this Oracle datatype is recommended in new code)

• you should declare the lenght of the column with char – the default is byte, which can have suprising behavior if your data has multi-byte characters

But since you explained that you are playing around and want to learn how to do that with a trigger, here is how the code would look like:

create or replace trigger biufer_customer
before insert or update
of f_name on customer
for each row
begin
    if (length(:new.f_name) <> 8) 
        then raise_application_error(-20001,'So I type my text here');
    end if;
end;

Within the trigger code, you can use pseudo table :new to access the values that are currently being updated or inserted; you would wrap the check in a if statement and raise the error accordingly.