Oracle trigger if inserting or updating

create trigger ins_or_upd before insert or update of salary on newemp
for each row
begin
if inserting then
insert into emp_audit values (:new.salary);
else
insert into emp_audit values(:old.salary);
end if;
end;
/

Oracle difference between before and after triggers

Try to compile 1. and 2. to see the difference:

1.
create or replace trigger upd_trigger
before update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
:new.salary := 1000;
end if;
end;
/

2.

create or replace trigger upd_trigger
after update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
:new.salary := 1000;
end if;
end;
/

However when you compile 3. and 4. you
will see no difference. And both these triggers
will have the same effect, i.e. they won't allow
the corresponding updates to take place.

Also, in 3. and 4. even if a single row has
its new salary < old salary, none of
the updates will be executed.


3.
create or replace trigger upd_trigger
before update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
raise_application_error(-20000,'not allowed');
end if;
end;
/

4.

create or replace trigger upd_trigger
after update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
raise_application_error(-20000,'not allowed');
end if;
end;
/

Oracle creating/removing new user

create user scott1 identified by tiger;
grant create session to scott1;
grant create table to scott1;
grant unlimited tablespace to scott1;

Drop user scott1 casecade;

Copying a table from another user's account :
create table booking as select * from scott.booking;

Sample oracle query

Table -> innings_runs (id Number, runs Number)

What to do : Select player ids who have made more than
100 runs without ever making 50 runs in a single innings.

ID RUNS
---------- ----------
1 48
1 48
1 48
2 50
2 51


select distinct(id) from innings_runs
where id in
(select id from (select id,sum(runs) as sum1 from innings_runs group by id having(sum(runs) > 100)))
and runs < 50
;

Oracle Sqlplus - changing user in an ongoing session

connect sys/orclk as sysdba
connect / as sysdba

Where to save files containing anonymous procedures in Oracle?

D:\oracle\product\10.2.0\db_3\BIN
where db_3 can be db_1/db_2 etc.
Just check your configuration of oracle.

Cursors In Oracle

declare
ans char(1);
begin
insert into newemp (empid,empname,job) values (109,'xyz','hr');
savepoint one_new_rec_ins;
update newemp set empname = 'abc' where empid = 109;
savepoint one_new_rec_upd;
delete from newemp where empid = 109;
ans := '&complete_rollback';
if ans = 'y' then
rollback to one_new_rec_ins;
elsif ans = 'n' then
rollback to one_new_rec_upd;
else
commit work;
end if;
end;

Parametrized Cursors

declare
cursor get_empl(salamt Number) is
select * from newemp where
salary > salamt and job <> 'President';
emprec newemp%rowtype;
begin
open get_empl(&amount);
dbms_output.put_line('records fetched are');
loop
fetch get_empl into emprec;
exit when get_empl%notfound;
dbms_output.put_line(emprec.empid||' '||emprec.empname||' '||emprec.job||' '||emprec.salary);
if emprec.job = 'manager'
then
update newemp set salary = salary + 500 where empid = emprec.empid;
elsif emprec.job = 'accountant' then
update newemp set salary = salary + 750 where empid = emprec.empid;
end if;
end loop;
close get_empl;
end;