Unable to create a trigger in Oracle SQL

Question

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.

In `VALUES (:NEW.employee_id, :OLD_Salary, :NEW.Salary...` should that be `:OLD.Salary`, a period instead of an underscore? Also, please consult your documentation for where to find compilation errors - [this seems to be how](https://stackoverflow.com/q/40421202/1270789)?. — Ken Y-N, Oct 14 '19 at 23:36
@AviralSrivastava: please do not edit the code in your questions to remove the errors which caused you to post the question. It makes it difficult (but not impossible) for people to understand why the question was asked in the first place. Thanks. — Bob Jarvis - Слава Україні, Oct 15 '19 at 02:14

score 1 · Accepted Answer · edited Oct 15 '19 at 00:30

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

score 1 · Answer 2 · answered Oct 15 '19 at 06:15

1

You can see the compilation error using DBA_ERRORS.

SELECT * FROM DBA_ERRORS  WHERE NAME = 'AUDIT_SALARIES';

answered Oct 15 '19 at 06:15

smita

298
1
4
17

score 0 · Answer 3 · answered Oct 15 '19 at 00:31

You tagged SQL Developer.

Are you using SQL Developer?

Because if you are...

We automatically do a 'show errors' for you on a compile when errors/warnings are returned. You can also see the compiler messages on the 'Compiler' tab - this should open automatically when you run it.

If you're not seeing this, I'm guessing you're on some version of SQL Developer where a bug is preventing that from happening, but I'm not aware of a version where that would be true.

score 0 · Answer 4 · answered Nov 14 '19 at 06:37

0

Try this it will solve you query:

SELECT * FROM DBA_ERRORS  WHERE NAME = 'AUDIT_SALARIES'

OR 

SELECT * FROM USER_ERRORS  WHERE NAME = 'AUDIT_SALARIES';

answered Nov 14 '19 at 06:37

chandni Patel

1
3

Please put your answer always in context instead of just pasting code. See [here](https://stackoverflow.com/help/how-to-answer) for more details. – gehbiszumeis Nov 14 '19 at 07:02
Ok sure will take care... and thanks for suggestion – chandni Patel Nov 14 '19 at 07:03

Unable to create a trigger in Oracle SQL

4 Answers4