User Tools

Site Tools


kurs:bonus_journal_trigger

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
kurs:bonus_journal_trigger [2008/11/12 14:57]
mh
kurs:bonus_journal_trigger [2014/09/10 21:22] (current)
Line 1: Line 1:
 +==== bonus_jn ====
 +<​code>​
 +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
 +/
 +</​code>​
 +
 +==== Trigger ====
 +
 +<​code>​
 +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;
 +/
 +
 +</​code>​
 +