This shows you the differences between two versions of the page.
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> | ||
+ | |||