oracle compound trigger to avoid mutating trigger

drop trigger trig_validate_sal;
drop trigger trig_before_up_sal;
drop trigger comp_tr_log_changes;
drop package pkg1;

drop table emp_t;
drop table emp_sal_log;

create table emp_t(empno number primary key, ename varchar2(100),deptno number, job varchar2(10),sal number);
create table emp_sal_log(empno number, update_log varchar2(1000));


insert into emp_t values(1000, 'KING',10,'CEO',150000);

insert into emp_t values(1001, 'RAVI',10,'DIRECTOR',80000);
insert into emp_t values(1002, 'SURYA',10,'SR MANAGER',70000);
insert into emp_t values(1003, 'RAGHU',10,'MANAGER',60000);
insert into emp_t values(1004, 'SCOTT',10,'LEAD',50000);
insert into emp_t values(1005, 'SMITH',10,'DEVELOPER',40000);
insert into emp_t values(1006, 'VARUN',10,'QA',40000);

commit;

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

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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


update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

update emp_t set sal = 160000 where empno = 1000;

select * from emp_sal_log;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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


select * from emp_t;

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

update emp_t set sal = 160000 where empno = 1000;

select * from emp_sal_log;

SQL to Convert Column to Rows

create table stu_table(Student varchar2(20),Subject  varchar2(20));
insert into stu_table values('A','Science');
insert into stu_table values('A','math');
insert into stu_table values('A','eng');
insert into stu_table values('B','Science');
insert into stu_table values('B','math');
insert into stu_table values('B','eng');
insert into stu_table values('C','math');
commit;

select * from stu_table;

select STUDENT, SUBJECT,max(student) over(partition by student) stu_GRP
from stu_table;

select SCIENCE, MATH, ENG
from (
select STUDENT, SUBJECT,STUDENT stu_GRP
from stu_table)
pivot(max(student) for subject in ('Science' as Science,'math' as math,'eng' as eng))
order by stu_GRP;


select STUDENT, max(decode(SUBJECT,'Science',STUDENT)) Science,
                max(decode(SUBJECT,'math',STUDENT)) math,
                max(decode(SUBJECT,'eng',STUDENT)) eng
select STUDENT, SUBJECT
from stu_table
group by STUDENT
order by student;

-------------------------------------------------------------------------------------------------

drop table t;
create table t(id number, val varchar2(30));

insert into t values(1,'amit');
insert into t values(1,'kumar');
insert into t values(1,'sharma');

insert into t values(2,'kishor');
insert into t values(2,'kumar');
insert into t values(2,'prasad');
insert into t values(2,'dev');

insert into t values(3,'siva');
insert into t values(3,'kumar');

commit;


select * from (select id,val,row_number() over(partition by id order by rownum) n_id
from t)
pivot(max(val) for n_id in (1,2,3,4));

SELECT
    id,max(decode(n_id,1,val)) v1,max(decode(n_id,2,val)) v2,max(decode(n_id,3,val)) v3,max(decode(n_id,4,val)) v4
FROM
    (
        SELECT
            id,
            val,
            ROW_NUMBER() OVER(PARTITION BY id ORDER BY ROWNUM) n_id
        FROM
            t
    )
GROUP BY ID;

-------------------------------------------------------------------------------------------------