Differences

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

--- kurs:bonus_journal_trigger [2008/11/12 14:57]
mh
+++ kurs:bonus_journal_trigger [2010/05/18 11:15]
127.0.0.1 external edit
@@ 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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools