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; 
/

Can you explain what do you mean by "can't get good result" ? — Shashank Kadne
– Shashank Kadne, Commented Feb 6, 2013 at 6:46
i mean I've got run time error dude..Error report: ORA-01748: only simple column names allowed here 01748. 00000 - "only simple column names allowed here" *Cause: *Action: — Thiyagu ATR
– Thiyagu ATR, Commented Feb 6, 2013 at 6:49
Just specify the column name salary and not the qualified column name emp.salary. Same applies to employee_id — Shashank Kadne
– Shashank Kadne, Commented Feb 6, 2013 at 7:05
ya I have tried this too dude...now it is compiled but,when i try to update salary it is giving error!is:Error report: SQL Error: ORA-04091: table HR.EMP is mutating, trigger/function may not see it ORA-06512: at "HR.SAL_UPDATE", line 5 ORA-04088: error during execution of trigger 'HR.SAL_UPDATE' 04091. 00000 - "table %s.%s is mutating, trigger/function may not see it" — Thiyagu ATR
– Thiyagu ATR, Commented Feb 6, 2013 at 7:12
That's tricky.Can you try with an after trigger instead of before Because in your case it is trying to read the column value salary which is not yet updated. — Shashank Kadne
– Shashank Kadne, Commented Feb 6, 2013 at 7:24

Egor Skriptunoff · Accepted Answer · 2013-02-06 07:39:16Z

1

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;

answered Feb 6, 2013 at 7:39

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

This is the correct way to go about it but your answer would be far better if you explained what problem the OP is happening. Namely that you shouldn't (can't in some situations) update the table on which the trigger is based when that is the trigger being triggered.

Fábio Oliveira · Accepted Answer · 2013-02-06 07:48:48Z

0

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).

answered Feb 6, 2013 at 7:48

Fábio Oliveira

2,33621 silver badges30 bronze badges

Collectives™ on Stack Overflow

how to create row level trigger in oracle?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related