CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
cre8eve
cre8eve

Reputation: 381

Subquery function inside trigger when clause

I want to create a trigger that prevents deletion of a record if student has AVG(RESULT) more than 3

  CREATE OR REPLACE TRIGGER DeleteStudentResults
  BEFORE DELETE ON SESSION_RESULTS
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  WHEN ((SELECT AVG(RESULT) from session_results where STUDENT_ID = :new.STUDENT_ID)>= 3)
  BEGIN
   raise_application_error (-20100, 'You can not delete initial record');
 END;

ER MODEL

But Oracle says it's forbidden to have SELECT statement inside when clause. How can I achieve such behaviour that deletion is prevented if average is more or equals than 3?

Upvotes: 0

Views: 1327

Answers (2)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

it is no good idea to solve this by a delete trigger

for example with session_results

id   studend_id   result
1    9            1
5    9            2
7    9            5

if you delete this student then it depends on the order the rows are processed. 

 delete id=1  avg(1,2,5)= 2.7
 delete id=5  avg(2,5)=3.5 --> raise error

but

 delete id=7  avg(1,2,5)= 2.7
 delete id=1  avg(1,2)=1.5 
 delete id=5  avg(2)=2

try to solve this by materialized view, for details see http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html

Upvotes: 1

Smart003
Smart003

Reputation: 1119

take it as suggestion

  CREATE OR REPLACE TRIGGER DeleteStudentResults
  BEFORE DELETE ON SESSION_RESULTS
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  declare 
  avg_result number;
  BEGIN
  (SELECT AVG(RESULT) into avg_result from session_results where STUDENT_ID = :old.STUDENT_ID);
  if(avg_result>=3) then
   raise_application_error (-20100, 'You can not delete initial record');
   end if;
 END;

Upvotes: 2

Related Questions