how to create row level trigger in oracle?

Question

I have one table in the hr schema called employee, I need to create row level trigger on this table so that whenever I try to update salary on employee table I ensure that salary not to be decreased!

I have tried this one but I get an error:

Error report: ORA-01748: only simple column names allowed here 01748. 00000 - "only simple column names allowed here

CREATE or REPLACE TRIGGER salary_dec_trigger 
BEFORE UPDATE OF emp.salary
ON emp 
FOR EACH ROW 
BEGIN 
if(:new.salary>:old.salary)
then

update emp set emp.salary=emp.salary+:new.salary where emp.employee_id=:new.employee_id;
else 
rollback;
end if;
END; 
/ 

Answer 1

As far as I can tell from your code you're trying that whenever you update a salary for an employee you'll actually sum it with his previous salary, right? And only allow increases in salary, never a decrease.

So why don't you just say :new.salary = :old.salary + :new.salary? And skip the rollback, just do :new.salary = :old.salary;

If it doesn't work you should try a procedure with an autonomous transaction to do that but that's what a before trigger should allow you to do directly (check this here).

Answer 2

CREATE or REPLACE TRIGGER salary_dec_trigger 
BEFORE UPDATE OF salary ON emp 
FOR EACH ROW 
BEGIN 
  if(:new.salary < :old.salary) then
    raise_application_error(-20001, 'Salary can''t be decreased');
  end if;
END;