-1

Below image is showing error.

error screenshot

I have created this simple trigger in plsql.

 CREATE OR REPLACE TRIGGER TR6
 BEFORE UPDATE ON EMPLOYEE
 FOR EACH ROW
 BEGIN
 INSERT INTO PRAC14 (EMP_NO,EMP_NAME,EMP_SAL,DEPT_NO)
 VALUES(:OLD.EMP_NO,:OLD.EMP_NAME,:OLD.EMP_SAL,:OLD.DEPT_NO); 
 END;
 /

after running above code it shows TRIGGER CREATED. Then when I am updating my employee table like

UPDATE EMPLOYEE
SET EMP_SAL=3000
WHERE DEPT_NO=10

I am getting this error message

ORA-04098: trigger 'Username.TR' is invalid and failed re-validation.I am using Oracle database 10g express edition.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ridham Karia
  • 23
  • 5
  • Trigger is created but it is in INVALID state, so it cannot be executed. You must revise and correct it. There must be some kind of error in the code (sorry, I don't have any Oracle for validate your code). – David Apr 03 '20 at 10:04
  • 1
    Your 2nd commend is wrong, @David. What would `:new.old.emp_no` be? – Littlefoot Apr 03 '20 at 10:13
  • I have mentioned :OLD.attribute_name pseudo record name then why I have to include :new.OLD.attribute_name?? – Ridham Karia Apr 03 '20 at 10:14
  • Totally correct Littlefoot. Thanks. – David Apr 03 '20 at 10:17
  • 1
    Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer Apr 03 '20 at 11:34

2 Answers2

0

Note the difference:

CREATE OR REPLACE TRIGGER TR6
                          ---

vs. 

ORA-04098: trigger 'Username.TR' is invalid 
                             --

It seems that there's yet another trigger on that table which is invalid. Either fix it, or drop it.

Check triggers by running

select table_name, trigger_name, trigger_type from user_triggers order by 1, 2;

[Example]

It is based on Scott's EMP table. Trigger intentionally uses :old.empnox, a column which doesn't exist. It leads to error you got:

SQL> create table prac14  as select empno, ename, sal, deptno
  2  from emp where 1 = 2;

Table created.

SQL>
SQL> create or replace trigger tr6
  2    before update on emp
  3    for each row
  4  begin
  5    insert into prac14 (empno,ename,sal,deptno)
  6    values(:old.empnox,:old.ename,:old.sal,:old.deptno);
  7  end;
  8  /

Warning: Trigger created with compilation errors.

SQL>
SQL> update emp set sal = 3000 where deptno = 10;
update emp set sal = 3000 where deptno = 10
       *
ERROR at line 1:
ORA-04098: trigger 'SCOTT.TR6' is invalid and failed re-validation

But, if trigger code is fixed, everything runs OK:

SQL> create or replace trigger tr6
  2    before update on emp
  3    for each row
  4  begin
  5    insert into prac14 (empno,ename,sal,deptno)
  6    values(:old.empno,:old.ename,:old.sal,:old.deptno);
  7  end;
  8  /

Trigger created.

SQL>
SQL> update emp set sal = 3000 where deptno = 10;

3 rows updated.

SQL>

I suggest you post the same, executed in your database.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

I have created multiple trigger for DML operation on a single table. That is why I am getting error. I have dropped all other triggers on EMPLOYEE table except TR6 and it worked.

Kudos to you @Littlefoot.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ridham Karia
  • 23
  • 5