如何检查PL/SQL中的varchar2变量是否像触发器中的10个字符?它会自动插入吗?它很好吗?
--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;
我会使用检查约束,而不是触发器:
alter table tblTenChars add constraint checkthings
check (length(justTenVars) = 10);
检查约束更简单、更有效。
但为了完整起见,触发代码是:
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;
如果引发异常,则中止插入或更新;否则就会发生。