1

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;
Sam
  • 7,252
  • 16
  • 46
  • 65
Chris_45
  • 8,769
  • 16
  • 62
  • 73

1 Answers1

3

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Ok but what's the story on> for each row? When I have it compiles with errors when I don't have it doesn't compile due to 'not allowed with table-level-triggers'? – Chris_45 May 24 '11 at 12:07
  • Allright, but i still have errors > 'PLS-00103: ...FOR when following where expected: begin function pragma procedure subtype type current cursor delete exists prior' – Chris_45 May 24 '11 at 12:13
  • Ok i got it, I couldnt have a declare part and and exception part. Cant you have declare variables and stuff in triggers? – Chris_45 May 24 '11 at 12:21
  • Working: 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'); end if; end; – Chris_45 May 24 '11 at 12:23
  • Yes you can have `declare` - between `for each row` and `begin` – Tony Andrews May 24 '11 at 12:24
  • Not working> create or replace trigger checkthings before insert or update on tblTenChars declare nogood exception; for each row begin if length(:new.justTenVars) <> 10 then raise nogoodpass; end if; exception when nogood then raise_application_error(-20008, 'Wrong not 10'); end; – Chris_45 May 24 '11 at 12:25