— Trigger to check if the sal greater than 100000, if yes, log the error message, else update sucessfully
create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
lv_max_sal number := 100000;
begin
if :new.sal < lv_max_sal then
insert into emp_sal_log values(:new.empno,
'Salary Updates successfully : '
||' OLD SAL = '||:old.sal||', NEW SAL = '||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
'Salary NOT UPDATED : Employee salary cannot be more than '
||lv_max_sal);
end if;
end;
/
————————————————————————————————————————————————————————–
— Trigger to check if the sal greater than sal of CEO, if yes, log the error message, else update successfully., but for CEO update should go through.
create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
lv_ceo_sal number;
begin
select sal into lv_ceo_sal
from emp_t
where job = 'CEO'
and deptno= :new.deptno;
if (:new.sal < lv_ceo_sal and :old.job ‘CEO’) or (:old.job = ‘CEO’) then
insert into emp_sal_log values(:new.empno,
‘Salary Updates successfully : ‘
||’ OLD SAL = ‘||:old.sal||’, NEW SAL = ‘||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
‘Salary NOT UPDATED : Employee salary cannot be more than ‘
||lv_ceo_sal);
end if;
end;
/
————————————————————————————————————————————————————————–
— Lets see how to avoid mutating error using packaged global variable
drop trigger trig_validate_sal;
create or replace package pkg1 as
lv_ceo_sal number;
end;
/
create or replace trigger trig_before_up_sal
before update of sal on emp_t
declare
lv_max_sal number;
begin
select sal into pkg1.lv_ceo_sal
from emp_t
where job = ‘CEO’;
end;
/
create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
begin
if (:new.sal < pkg1.lv_ceo_sal and :old.job ‘CEO’)
or (:old.job = ‘CEO’) then
insert into emp_sal_log values(:new.empno,
‘Salary Updates successfully : ‘
||’ OLD SAL = ‘||:old.sal||’, NEW SAL = ‘||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
‘Salary NOT UPDATED : Employee salary cannot be more than ‘
||pkg1.lv_ceo_sal);
end if;
end;
/
————————————————————————————————————————————————————————–
— Lets see how to avoid mutating error using compound trigger
drop trigger trig_validate_sal;
drop trigger trig_before_up_sal;
drop package pkg1;
CREATE OR REPLACE TRIGGER comp_tr_log_changes
FOR UPDATE ON EMP_T COMPOUND TRIGGER
lv_ceo_sal number;
BEFORE STATEMENT IS
BEGIN
select sal
into lv_ceo_sal
from emp_t
where job = ‘CEO’;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
if (:new.sal < lv_ceo_sal and :old.job ‘CEO’) or (:old.job = ‘CEO’) then
insert into emp_sal_log values(:new.empno,
‘Salary Updates successfully : ‘||’ OLD SAL = ‘||:old.sal||’, NEW SAL = ‘||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
‘Salary NOT UPDATED : Employee salary cannot be more than ‘||lv_ceo_sal);
end if;
END BEFORE EACH ROW;
END comp_tr_log_changes;
/
