sql - Trigger with a function Oracle -
i need make trigger insert 'a' in case of approved student , 'r' not approved student. need use function returns 'a' or 'r'. here function:
create or replace function check_grade (grade in number, frequency in number) return varchar2 result varchar2(1) default ''; begin if(grade>=6) , (frequency>=0.75)then result := 'a'; else result := 'r'; end if; return result; end;
and here trigger:
create or replace trigger situation before insert on student each row begin select check_grade(grade, frequency) : new.situation dual; end;
when try execute error. don't have idea in can do!
user-defined functions allowed in triggers, , function looks ok.
one definite problem trigger mentions grade
, frequency
, variable names, , haven't been declared. i'll assume meant use inserted column values instead: :new.grade
, :new.frequency
.
also, i've set "new" values using simple variable assignment (:new.value := blah
instead of select blah :new.value dual
). way may work (or may not - don't know), simple variable assignment syntax lot shorter , it's easy enough read.
so try replacing these 3 lines:
select check_grade(grade, frequency) : new.situation dual;
... 1 line:
:new.situation := check_grade(:new.grade, :new.frequency);
if doesn't work please update question , replace "i error" i error ora-<actual-error-number>. include stack trace, show if function or trigger throwing.
Comments
Post a Comment