CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Brandon Tupiti
Brandon Tupiti

Reputation: 274

Using trigger to update a value

How do I write a simple trigger to check when salary is updated it is not over 5% for an existing faculty member.

I have a block of code here.

create or replace TRIGGER TRG_Not_over_5per
BEFORE UPDATE OF F_SALARY ON FACULTY
DECLARE 
sal FACULTY.F_SALARY%TYPE;
BEGIN
SELECT FACULTY.F_SALARY INTO sal FROM FACULTY
-- how do I use the WHERE CLAUSE here so that I get the salary affected

-- do some math on the first and second salary
-- if less than 5%, keep update, if not, reject update.
END;

Thanks in advance.

Upvotes: 0

Views: 406

Answers (1)

APC
APC

Reputation: 146239

You don't need to use a SELECT in the trigger (*). Define the trigger as FOR EACH ROW and you can have access to the old and new values of any column belonging to the table.

create or replace TRIGGER TRG_Not_over_5per
BEFORE UPDATE OF F_SALARY ON FACULTY
FOR EACH ROW

So your code would look like:

if :new.f_salary < :old.f_salary * 1.05 then
     raise_application_error (
       -20000
       , 'salary increase must be at least 5%'
    );
end if;

This way of handling the rule violation is just a suggestion. You do whatever you need to. You don't need to handle the ELSE branch: Oracle will apply the update by default.

(*) In fact Oracle will hurl a mutating table exception if you do try to execute the query you want to write. Find out more.

Upvotes: 3

Related Questions