CODEWITHSUNDEEP
oracle-databaseplsqltriggersdatabase-trigger
knt5784
knt5784

Reputation: 129

Difficulty compiling an AFTER INSERT OR UPDATE trigger

I have an EMPLOYEE table with SALARY field. I'm using Oracle SQL developer. I want to write a trigger so that when someone update salary in EMPLOYEE table, it will update Salary field in EMPLOYEE_SALARIES table as low, medium, high. Here's the second table.

CREATE TABLE Employee_Salaries(
Ssn CHAR(9) NOT NULL,
Salary VARCHAR(10),
Log_Date DATE
);

Here's the trigger and procedure to update the Salary field to low, middle or high. 

CREATE OR REPLACE PROCEDURE salaryType(x IN NUMBER, y OUT VARCHAR) IS
BEGIN
    IF x >= 60000 THEN y := 'HIGH';
    ELSIF (x >= 40000 AND x <= 60000) THEN y := 'MEDIUM';
    ELSE y := 'LOW'; 
    END IF;
END salaryType;
/

I get compiler error on this trigger. Please tell me what I did wrong or am I missing something.

CREATE OR REPLACE TRIGGER salary1
    AFTER INSERT OR UPDATE ON Employee
    FOR EACH ROW
BEGIN
    DECLARE
        salaryRank VARCHAR(10) := ' ';
    salaryType(:new.Salary, salaryRank); 
    INSERT INTO Employee_Salaries(Ssn, Salary, Log_Date) VALUES (:new.Ssn, salaryRank, SYSDATE);
END;
/

Upvotes: 1

Views: 57

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

Declaration Part is at wrong place(should be before BEGIN and just after FOR EACH ROW statement of TRIGGER's header), Make it as the following :

CREATE OR REPLACE TRIGGER salary1
    AFTER INSERT OR UPDATE ON Employee
    FOR EACH ROW
DECLARE
        salaryRank VARCHAR(10) := ' ';      
BEGIN
    salaryType(:new.Salary, salaryRank);
    INSERT INTO Employee_Salaries(Ssn, Salary, Log_Date) VALUES (:new.Ssn, salaryRank, SYSDATE);
END;

Upvotes: 2

user5683823
user5683823

Reputation:

The keyword BEGIN in the trigger is in the wrong place. It should come after the DEFINE block; that is, after you declare salaryrank and before you invoke the procedure.

Upvotes: 0

Related Questions