ORA-04079:invalid trigger specification

Question

create or replace trigger fineCalc
AFTER UPDATE ON book_issue
for each row
when ((new.date_of_return-old.date_of_issue)>7) 
declare 
  rcpt_no  number;
  s_id  char(10);
begin
  if :old.card_id in (select card_id from STUDENT_BOOK_ISSUE)
  then
    select max(receipt_no) into rcpt_no from fine;
    select student_id into s_id from STUDENT_BOOK_ISSUE sbi where sbi.card_id=:old.card_id;

    insert into fine values( rcpt_no+1,((:NEW.date_of_return-:OLD.date_of_issue-7)*5),s_id);
  end if;
end;

This gets ORA-04079: invalid trigger specification in Apex's SQL Workshop:

Oracle Apex screenshot

How can I fix it?

That gets PLS-00405: subquery not allowed in this context, not ORA-04079. You can't have a subquery as part of an `if`, [you need to get the query result into a variable](http://stackoverflow.com/a/29134619/266304). Please see [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask). Showing the actual code you're running and the error you get would be a good start. — Alex Poole, Apr 12 '16 at 08:52
i am performing the trigger operation in oracle 11g....then it is showing ORA:04079 error while on oracle SQL Developer its showing PLS-00405:Subquery not allowed in this context.....what should i do now — abhishek jadhav patil, Apr 12 '16 at 08:58
Not relevant to the error, but using `max(receipt_no)` isn't a good idea; it's [better to use a sequence](http://stackoverflow.com/q/17210835/266304). — Alex Poole, Apr 12 '16 at 09:09
Where are you seeing the ORA-04079? Are you sure that isn't still just on screen from an earlier compilation attempt? You seem to suggest "in oracle 11g" means a different client to SQL Developer, but that's the database version. If you're getting different behaviour in two clients then tell us what they both are, including the versions you're using. — Alex Poole, Apr 12 '16 at 09:10
i am sure that i am still getting same error .....if there is way to send screen shot i'll surely send it... — abhishek jadhav patil, Apr 12 '16 at 09:14
Open a new window. And run the code again. If you see error, take screenshot and attach (edit the question, there you will see option to attach) — Utsav, Apr 12 '16 at 09:19
i am getting PLS-00405 error on SQL developer...and ORA-04079 on oracle 11g...pls give me solution for Oracle 11g — abhishek jadhav patil, Apr 12 '16 at 09:28

score 1 · Accepted Answer · edited Apr 12 '16 at 09:06

1

As Alex suggested, move the if condition outside and try again.

create or replace trigger fineCalc
AFTER UPDATE ON book_issue
for each row
when ((new.date_of_return-old.date_of_issue)>7) 
declare 
  rcpt_no  number;
  s_id  char(10);
  v_count number;
begin
  select count(*) into v_count from STUDENT_BOOK_ISSUE where card_id=:old.card_id;
  if v_count > 0
  then
    select max(receipt_no) into rcpt_no from fine;
    select student_id into s_id from STUDENT_BOOK_ISSUE sbi where sbi.card_id=:old.card_id;
    insert into fine values( rcpt_no+1,((:NEW.date_of_return-:OLD.date_of_issue-7)*5),s_id);
  end if;
end;

edited Apr 12 '16 at 09:06

Alex Poole

183,384
11
179
318

answered Apr 12 '16 at 08:59

Utsav

7,914
2
17
38

Sorry the `begin` clause wasnt at right place. Thanks @alex from correcting it. @abhishekjadhavpatil - Try the updated answer and check again. – Utsav Apr 12 '16 at 09:13
there is no underscore between book and issue on second line. And there is no `;` after line 13 after `fine`. Looks like you didn't copy the code which was given in answer. Fix it. – Utsav Apr 12 '16 at 09:35
i have made changes according to suggestion but still i am getting same error. – abhishek jadhav patil Apr 12 '16 at 09:40
thanks Alex and Utsav....my trigger is created and updating database successfully – abhishek jadhav patil Apr 12 '16 at 09:49

ORA-04079:invalid trigger specification

1 Answers1