dokuwiki

CREATE TABLE bonus_jn
    (bjn_id                         NUMBER,
    employee_id                    NUMBER(6,0),
    last_name                      VARCHAR2(25),
    first_name                     VARCHAR2(20),
    salary                         NUMBER(8,2),
    old_salary                     number,
    bonus                          NUMBER(38,3),
    change_date                    DATE,
    change_type                    VARCHAR2(1))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
/

CREATE OR REPLACE TRIGGER trg_bonus_jn
 BEFORE
  INSERT OR DELETE OR UPDATE
 ON bonus
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
  v_type varchar(10);

begin
if inserting then
  v_type := 'I';
elsif deleting then
  v_type := 'D';
else
  v_type := 'U';
end if;

  insert into bonus_jn (
    BJN_ID,
    EMPLOYEE_ID,
    LAST_NAME,
    FIRST_NAME,
    SALARY,
    old_salary,
    BONUS,
    change_date,
    change_type)
  values (
    hr_seq.nextval,
    nvl(:old.EMPLOYEE_ID, :new.EMPLOYEE_ID),
    nvl(:old.last_name,   :new.last_name),
    nvl(:old.first_name,  :new.first_name),
    :new.salary,
    :old.salary,
    nvl(:old.bonus,       :new.bonus),
    sysdate,
    v_type
    );
end;
/