CREATE TABLE SUBSCRIBER_SUBSCRIPTION (
     SUBSCRIBER_ID NUMBER(5) NOT NULL,
     STATUS VARCHAR2(20),---which can be ACTIVE/INACTIVE
     EXPIRY_DATE DATE);

CREATE OR REPLACE TRIGGER SUBSCRIBER_SUBSCRIPTION_BRU
BEFORE UPDATE
OF STATUS
ON SUBSCRIBER_SUBSCRIPTION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.STATUS = 'INACTIVE'
      )
BEGIN
 :NEW.STATUS := to_char('IN_' || to_char(sysdate, 'YYYYMMDDHH24MI'));
 
   INSERT INTO SUBSCRIBER_SUBSCRIPTION_HIST (
     SUBSCRIBER_ID, STATUS,
     EXPIRY_DATE)
  VALUES (:OLD.SUBSCRIBER_ID, :NEW.STATUS,
      :OLD.EXPIRY_DATE);
 
 EXCEPTION
    WHEN OTHERS THEN
     -- Consider logging the error and then re-raise
    RAISE;
END;
/

CREATE OR REPLACE TRIGGER SUBSCRIBER_SUBSCRIPTION_BRU
BEFORE UPDATE
OF STATUS
ON SUBSCRIBER_SUBSCRIPTION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.STATUS = 'INACTIVE'
      )
----NEW CODE-----------------------------------
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
-----------------------------------------------
BEGIN
 :NEW.STATUS := to_char('IN_' || to_char(sysdate, 'YYYYMMDDHH24MI'));
 
   INSERT INTO SUBSCRIBER_SUBSCRIPTION_HIST (
     SUBSCRIBER_ID, STATUS,
     EXPIRY_DATE)
  VALUES (:OLD.SUBSCRIBER_ID, :NEW.STATUS,
      :OLD.EXPIRY_DATE);
COMMIT;

BEGIN
FOR i IN(SELECT * FROM SUBSCRIBER_SUBSCRIPTION_HIST)
LOOP

IF (i.STATUS LIKE '%IN%') THEN
DELETE FROM SUBSCRIBER_SUBSCRIPTION
WHERE SUBSCRIBER_ID=i.SUBSCRIBER_ID;
COMMIT;
END IF;
END LOOP;
END;

---COMMIT;

 EXCEPTION
    WHEN OTHERS THEN
     -- Consider logging the error and then re-raise
    RAISE;
END;
/

Before Trigger Creation Test Data
------------------------------------
INSERT INTO SUBSCRIBER_SUBSCRIPTION VALUES(111,'ACTIVE',SYSDATE+100);
INSERT INTO SUBSCRIBER_SUBSCRIPTION VALUES(222,'ACTIVE',SYSDATE+200);
INSERT INTO SUBSCRIBER_SUBSCRIPTION VALUES(333,'ACTIVE',SYSDATE+300);

COMMIT;

After Trigger Creation Test Data
------------------------------------
UPDATE SUBSCRIBER_SUBSCRIPTION
SET STATUS='INACTIVE'
WHERE SUBSCRIBER_ID=333;

COMMIT;

SCOTT@orcl_11gR2> CREATE TABLE subscriber_subscription
  2    (subscriber_ID  NUMBER(5) NOT NULL,
  3  	status	       VARCHAR2(20),
  4  	expiry_date    DATE)
  5  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE subscriber_subscription_hist
  2    (subscriber_ID  NUMBER(5) NOT NULL,
  3  	status	       VARCHAR2(20),
  4  	expiry_date    DATE)
  5  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER subscriber_subscription_bru
  2    BEFORE UPDATE OF status ON subscriber_subscription
  3    FOR EACH ROW
  4    WHEN (NEW.status = 'INACTIVE')
  5  BEGIN
  6    :NEW.status := 'IN_' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI');
  7    INSERT INTO subscriber_subscription_hist
  8  	 (subscriber_ID, status, expiry_date)
  9    VALUES (:OLD.subscriber_ID, :NEW.status, :OLD.expiry_date);
 10  END subscriber_subscription_bru;
 11  /

Trigger created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER subscriber_hist_ari
  2    AFTER INSERT ON subscriber_subscription_hist
  3    FOR EACH ROW
  4    WHEN (NEW.status LIKE 'IN%')
  5  DECLARE
  6    v_job  NUMBER;
  7  BEGIN
  8    DBMS_JOB.SUBMIT (v_job,
  9    'DELETE FROM subscriber_subscription
 10  	WHERE  subscriber_ID = ''' || :NEW.subscriber_ID || ''';');
 11  END subscriber_hist_ari;
 12  /

Trigger created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> INSERT ALL
  2  INTO SUBSCRIBER_SUBSCRIPTION VALUES(111,'ACTIVE',SYSDATE+100)
  3  INTO SUBSCRIBER_SUBSCRIPTION VALUES(222,'ACTIVE',SYSDATE+200)
  4  INTO SUBSCRIBER_SUBSCRIPTION VALUES(333,'ACTIVE',SYSDATE+300)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11gR2> UPDATE SUBSCRIBER_SUBSCRIPTION
  2  SET    STATUS = 'INACTIVE'
  3  WHERE  SUBSCRIBER_ID = 333
  4  /

1 row updated.

SCOTT@orcl_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11gR2> SELECT * FROM subscriber_subscription_hist
  2  /

SUBSCRIBER_ID STATUS               EXPIRY_DA
------------- -------------------- ---------
          333 IN_201106262023      21-APR-12

1 row selected.

SCOTT@orcl_11gR2> -- wait for job to finish running before checking results:
SCOTT@orcl_11gR2> exec dbms_lock.sleep (5)

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM subscriber_subscription
  2  /

SUBSCRIBER_ID STATUS               EXPIRY_DA
------------- -------------------- ---------
          111 ACTIVE               04-OCT-11
          222 ACTIVE               12-JAN-12

2 rows selected.

SCOTT@orcl_11gR2>

create table emp (emp_id integer not null, ename varchar2(10) not null, status varchar2(10));

alter table emp
   add constraint emp_pk primary key (emp_id)
   add constraint emp_Uk1 unique (ename)
/

create table emp_h (emp_id integer not null, ename varchar2(10) not null, status varchar2(10));

alter table emp_h
   add constraint emp_pk primary key (emp_id)
   add constraint emp_Uk1 unique (ename)
/


rename emp to emp_tab;

create or replace view emp
as
select *
from emp_tab
/

create or replace trigger io_emp
instead of insert or update or delete on emp
for each row
begin
--
-- do the original operation on the real table
--
   if inserting then null;
      insert into emp_tab value (:new.emp_id,:new.ename,:new.status);
   if updating then null;
      update emp_tab set
              emp_id = :new.emp_id
            , ename = :new.ename
            , status = :new.status
      where emp_id = :old.emp_id
      ;
   else null;
      delete
      from emp_tab
      where emp_id = :old.emp_id
      ;
   end if;

--
-- do the update logic
--
   if updating then;
      if updating('status') then
         if :new.status = 'INACTIVE' then
            insert into emp_h
            select emp_id,ename,status
            from emp_tab
            where emp_id = :new.emp_id
            ;
            delete from emp_tab where emp_id = :old.emp_id;
         end if;
      end if;
   end if;
end;
/
show errors