CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Renan Geraldo
Renan Geraldo

Reputation: 655

Trigger with a function Oracle

I need to make a trigger to insert 'A' in case of a approved student and 'R' for a not approved student. I need to use a function that returns 'A' or 'R'. Here is my function:

create or replace function check_grade
(grade in number, frequency in number) return varchar2
as 
result varchar2(1) default '';
begin
if(grade>=6) and (frequency>=0.75)then
result := 'A';
else
result := 'R';
end if;
return result;
end;

And here is my trigger:

create or replace trigger situation
before 
insert on student
for each row
begin
select check_grade(grade, frequency)
into : new.situation
from dual;
end;

When I try to execute I always get an error. I don't have idea in what i can do!

Upvotes: 0

Views: 75

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The function isn't needed here; it can be replaced by a CASE expression:

create or replace trigger situation
  before insert on student
  for each row
begin
  :new.situation := CASE
                      WHEN grade >= 6 and frequency >= 0.75 THEN 'A'
                      ELSE 'R';
                    END;
end situation;

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

User-defined functions are allowed in triggers, and your function looks OK.

One definite problem with your trigger is that it mentions grade and frequency, which are variable names, and they haven't been declared. I'll assume you probably meant to use the inserted column values instead: :new.grade and :new.frequency.

Also, I've always set "new" values using simple variable assignment (:new.value := blah instead of select blah into :new.value from dual). Your way may work (or may not - I don't know), but the simple variable assignment syntax is a lot shorter and it's easy enough to read.

So try replacing these three lines:

select check_grade(grade, frequency)
into : new.situation
from dual;

... with this one line:

:new.situation := check_grade(:new.grade, :new.frequency);

If this doesn't work then please update the question and replace "I always get an error" with I get error ORA-<actual-error-number>. Include the stack trace, which will show if the function or the trigger is throwing.

Upvotes: 1

Related Questions