Search code examples
variablesplsqlvarchar2

How do I check that a varchar2 variable in PL/SQL is like 10 characters in a trigger?


How do I check that a varchar2 variable in PL/SQL is like 10 characters in a trigger? And does it automatically proceed with the insert it its good?

--trigger that checks that number of characters are 10, doesnt work
create or replace trigger checkthings
before insert or update
on tblTenChars
declare
noGood exception;
begin
if :new.justTenVars(size) <> 10 then --this is not the way? 
raise noGood;
end if;
exception
when noGood then
raise_application_error(-20008, 'Wrong not 10 characters');
end;

Solution

  • I would use a check constraint, not a trigger:

    alter table tblTenChars add constraint checkthings
      check (length(justTenVars) = 10);
    

    A check constraint is simpler and more efficient.

    But for completeness, the trigger code would be:

    create or replace trigger checkthings
    before insert or update
    on tblTenChars
    for each row
    begin
      if length(:new.justTenVars) <> 10 then 
        raise_application_error(-20008, 'Wrong not 10 characters');
      end if;
    end;
    

    If the exception is raised, the insert or update is aborted; otherwise it takes place.