Search code examples
oracleplsqloracle-sqldeveloper

Unable to create a trigger in Oracle SQL

I want to create a trigger but it is tainted by a warning: trigger created with compilation errors. The query that I am running is:

CREATE OR REPLACE TRIGGER Audit_Salaries
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
    IF (:NEW.Salary > :OLD.Salary*1.20) THEN
        INSERT INTO Salary_Audit (EmployeeID, OldSalary, NewSalary,Username, ChangeDate)
            VALUES (:NEW.employee_id, :OLD.Salary,:NEW.Salary, user, sysdate);
    END IF;
 END;
/ 

Warning: Trigger created with compilation errors.

And this is the result that I am getting:

Warning: Trigger created with compilation errors.

I tried reading other similar answers but the solutions that are given there already exist in mine(syntax).

Due to this, when I log into the different user and run the query, it says the trigger is at fault or not created properly, re-validation failed.

I expect the trigger to be created without any compilation errors along with the understanding of what is wrong in my query.

Solution

  • To see the details of the compilation error, you can query system view USER_ERRORS (or DBA_ERRORS):

    SELECT * FROM USER_ERRORS  WHERE NAME = 'AUDIT_SALARIES';

    I cannot reproduce the error that you are getting, your code compiles successfully when I run it on 11gR2 and 18c. I can only imagine that there is an error in the column names of source table employees or target table salary_audit.

    Demo on DB Fiddle