ORA-04098: trigger is invalid and failed re-validation error

Question

Why this procedure can't run? It compiles correctly but when i try to insert something it shows me this error: ORA-04098: trigger 'SAMPLEDATA.BI_STUDENTS' is invalid and failed re-validation.

create or replace PROCEDURE insert_new_student (
  pName   NVARCHAR2, 
  pPhone NVARCHAR2,
  pReg DATE,      
  pBirth DATE
)
AS
  -- declare variables.
  v_student_id NUMBER;
BEGIN
  -- add a record.
  INSERT INTO STUDENTS (STUDENT_ID, NAME, PHONENUMBER, REGISTRATIONDATE, BIRTHDAY)
  VALUES (student_id_seq.nextval, pName, pPhone, pReg, pBirth)
  RETURNING STUDENT_ID INTO v_student_id;
END insert_new_student;

//

BEGIN
  insert_new_student( 'A', '012345', SYSDATE, '01/26/2017');
END

//

CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_STUDENTS" 
  before insert on "STUDENTS"               
  for each row  
begin   
  if :NEW."STUDENT_ID" is null then 
    select "STUDENTS_SEQ".nextval into :NEW."STUDENT_ID" from sys.dual; 
  end if; 
end;

1. Check whether you have a trigger on that table for Insert operations: 2. Try to Insert directly, without procedure. — Ikrom
Check your triggers: select so.name, text from sysobjects so, syscomments sc where type = 'TR' and so.id = sc.id and text like '%YoutTableName%' ; — Ikrom
If you disable the trigger and then try again, it'll probably work. You might want to edit your question and add the trigger source code, too. — kfinity
I didn't create trigger but when i write select * from all_triggers where table_name = 'STUDENTS' it showed me that there is trigger on that table. — J.D.

TurtlesAllTheWayDown TurtlesAllTheWayDown · Accepted Answer · 2017-11-03T18:52:06

Run the following:
SHOW ERRORS TRIGGER SAMPLEDATA.BI_STUDENTS;

What is the output?

Run the following:
select * from user_errors where type = 'TRIGGER' and name = 'BI_STUDENTS';

What is the result?

ORA-04098: trigger is invalid and failed re-validation error

1 Answers