drop table PROD cascade constraints
/

create table PROD
(
   ID NUMBER ,
   NUME VARCHAR2(30) ,
   STARE NUMBER DEFAULT 1
)
/

drop table HISTORY cascade constraints
/

create table HISTORY
(
   ID NUMBER ,
   NUME VARCHAR2(30) ,
   STARE NUMBER
)
/

Insert into PROD
(ID, NUME)
Values
(1, 'monica')
/
Insert into PROD
(ID, NUME)
Values
(2, 'ibanes')
/
Insert into PROD
(ID, NUME)
Values
(3, 'carmen')
/

Commit
/

CREATE OR REPLACE
Package update_prod
IS
type prod_rec IS RECORD(
      row_id ROWID,
      id prod.ID%type,
      nume prod.NUME%type,
      stare prod.STARE%type );
type prod_tab_type is table of prod_rec index by binary_integer;
prod_tab prod_tab_type;
idx binary_integer;
END;
/

CREATE OR REPLACE TRIGGER BEFORE_IUD_PROD_STATEMENT
BEFORE INSERT or DELETE or UPDATE
ON PROD
Begin
   update_prod.idx := 0;
End;
/

CREATE OR REPLACE TRIGGER AFTER_UPDATE_PROD_ROW
AFTER UPDATE
OF STARE
ON PROD
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.stare=0)
Begin
   update_prod.idx := update_prod.idx + 1;
   update_prod.prod_tab(update_prod.idx).row_id := :new.rowid;
   update_prod.prod_tab(update_prod.idx).id := :old.id;
   update_prod.prod_tab(update_prod.idx).nume := :new.nume;
   update_prod.prod_tab(update_prod.idx).stare := :new.stare;
End;
/

CREATE OR REPLACE TRIGGER AFTER_UPDATE_PROD_STATEMENT
AFTER UPDATE
OF STARE
ON PROD
Begin
for i in 1..update_prod.idx loop
   delete from prod where rowid = update_prod.prod_tab(i).row_id;
   insert into history(id,nume,stare)
   values (update_prod.prod_tab(i).id, update_prod.prod_tab(i).nume,    update_prod.prod_tab(i).stare);
end loop;
update_prod.idx := 0;
End;
/

prompt select * from prod
select * from prod
/
prompt select * from history
select * from history
/
prompt update prod set stare=0 where id=1

update prod
set stare=0
where id=1
/
prompt select * from prod
select * from prod
/
prompt select * from history
select * from history
/

prompt update prod set stare=0 where id=2

update prod
set stare=0
where id=2
/

prompt select * from prod
select * from prod
/
prompt select * from history
select * from history
/