kurs:bonus_journal_trigger
Differences
This shows you the differences between two versions of the page.
|
kurs:bonus_journal_trigger [2010/05/18 11:15] 127.0.0.1 external edit |
kurs:bonus_journal_trigger [2014/09/10 21:22] |
||
|---|---|---|---|
| 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), | ||
| - | nvl(:new.salary, null), | ||
| - | nvl(:old.salary, null), | ||
| - | nvl(:old.bonus, :new.bonus), | ||
| - | sysdate, | ||
| - | v_type | ||
| - | ); | ||
| - | end; | ||
| - | / | ||
| - | |||
| - | </code> | ||
| - | |||
kurs/bonus_journal_trigger.txt ยท Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
