Oracle - Trigger is invalid and failed to re-validation

I get error "Trigger is invalid and failed to re-validation" about my trigger when update table. Here is my trigger:

create or replace trigger t_backup_sal before update on employees
for each row 
begin
    merge into sal_backup sb using employees e
    on (sb.emp_id = e.emp_id) 
    when matched then 
      update set sb.emp_id = e.emp_id,
                 sb.salary = e.salary,
                 sb.time_change = sysdate
    when not matched then 
      insert (sb.emp_id, sb.salary, sb.time_change)
      values (e.emp_id, e.salary, sysdate)
end;

How to solve this error?

Error you got means that you performed action (updated employees table) which caused trigger to fire. However, as trigger is invalid, Oracle can't execute its code and informed you about it.

Query user_errors for more info.

Sample tables:

SQL> create table employees
  2  (emp_id number, salary number);

Table created.

SQL> create table sal_backup
  2  (emp_id number, salary number, time_change date);

Table created.

Trigger:

SQL> create or replace trigger t_backup_sal before update on employees
  2  for each row
  3  begin
  4      merge into sal_backup sb using employees e
  5      on (sb.emp_id = e.emp_id)
  6      when matched then
  7        update set sb.emp_id = e.emp_id,
  8                   sb.salary = e.salary,
  9                   sb.time_change = sysdate
 10      when not matched then
 11        insert (sb.emp_id, sb.salary, sb.time_change)
 12        values (e.emp_id, e.salary, sysdate)
 13  end;
 14  /

Warning: Trigger created with compilation errors.

What's wrong?

SQL> select line, position, text
  2  from user_Errors
  3  where name = 'T_BACKUP_SAL';

      LINE   POSITION TEXT
---------- ---------- ----------------------------------------------------------------------
        10         44 PL/SQL: ORA-00933: SQL command not properly ended
         2          5 PL/SQL: SQL Statement ignored
        11          4 PLS-00103: Encountered the symbol "end-of-file" when expecting one of
                      the following:

                         ( begin case declare end exception exit for goto if loop mod
                         null pragma raise return select update while with
                         <an identifier> <a double-quoted delimited-identifier>
                         <a bind variable> << continue close current delete fetch lock
                         insert open rollback savepoint set sql execute commit forall
                         merge pipe purge


SQL>

Basically, it says that you're missing a semi-colon which terminates the merge statement:

SQL> create or replace trigger t_backup_sal before update on employees
  2  for each row
  3  begin
  4      merge into sal_backup sb using employees e
  5      on (sb.emp_id = e.emp_id)
  6      when matched then
  7        update set sb.emp_id = e.emp_id,
  8                   sb.salary = e.salary,
  9                   sb.time_change = sysdate
 10      when not matched then
 11        insert (sb.emp_id, sb.salary, sb.time_change)
 12        values (e.emp_id, e.salary, sysdate);    --> here
 13  end;
 14  /

Trigger created.

SQL>

So, does it work now?

SQL> insert into employees (emp_id, salary) values (1, 100);

1 row created.

SQL> update employees set salary = 200;
update employees set salary = 200
       *
ERROR at line 1:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "SB"."EMP_ID"
ORA-06512: at "SCOTT.T_BACKUP_SAL", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_BACKUP_SAL'


SQL>

Well, that's another error.

Back to square 1:

SQL> create or replace trigger t_backup_sal before update on employees
  2  for each row
  3  begin
  4      merge into sal_backup sb using employees e
  5      on (sb.emp_id = e.emp_id)              -- sb.emp_id is used here
  6      when matched then
  7        update set -- sb.emp_id = e.emp_id,  -- you can't update that column, and it
  8                   sb.salary = e.salary,     -- doesn't make sense to do so
  9                   sb.time_change = sysdate
 10      when not matched then
 11        insert (sb.emp_id, sb.salary, sb.time_change)
 12        values (e.emp_id, e.salary, sysdate);
 13  end;
 14  /

Trigger created.

SQL>

Repeat:

SQL> update employees set salary = 200;
update employees set salary = 200
       *
ERROR at line 1:
ORA-04091: table SCOTT.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T_BACKUP_SAL", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_BACKUP_SAL'


SQL>

Another error! What does that mean? It means that you can't select from a table which is just being modified. Use pseudorecord, instead (identified by :new. and :old. for ID (which is always the same - I hope you aren't modifying employee's ID!?) and salary (as you probably want to backup previous value).

You know the drill: back to square 1:

SQL> create or replace trigger t_backup_sal before update on employees
  2  for each row
  3  begin
  4      merge into sal_backup sb
  5        using (select :new.emp_id as emp_id,
  6                      :new.salary as salary
  7               from dual) e
  8      on (sb.emp_id = e.emp_id)
  9      when matched then
 10        update set sb.salary = e.salary,
 11                   sb.time_change = sysdate
 12      when not matched then
 13        insert (sb.emp_id, sb.salary, sb.time_change)
 14        values (e.emp_id, e.salary, sysdate);
 15  end;
 16  /

Trigger created.

Does it work now? Yes!

SQL> update employees set salary = 200;

1 row updated.

Result:

SQL> select * from sal_backup;

    EMP_ID     SALARY TIME_CHA
---------- ---------- --------
         1        100 09.08.23

SQL>