Oracle triggers if inserting updating

Rated 4.9/5 based on 652 customer reviews

Lets create a compound trigger for auditing a large table called ’employees’. SALARY); v_emp_changes(v_index).to_value := to_char(: NEW. SALARY); v_emp_changes(v_index).action := 'Update'; END IF; END IF; IF v_index = v_threshhold THEN flush_logs(); END IF; END AFTER EACH ROW; -- AFTER STATEMENT Section: AFTER STATEMENT IS BEGIN flush_logs(); END AFTER STATEMENT; END aud_emp; / INSERT INTO employees VALUES (1, 'emp1', 10000); INSERT INTO employees VALUES (2, 'emp2', 20000); INSERT INTO employees VALUES (3, 'emp3', 16000); UPDATE employees SET salary = 2000 WHERE salary EMP_ID, UPD_BY, UPD_DT, ACTION, FIELD, FROM_VALUE, TO_VALUE 1, Aditya,1/22/2014 AM, Create,*, NULL,* 2, Aditya,1/22/2014 AM, Create,*, NULL,* 3, Aditya,1/22/2014 AM, Create,*, NULL,* 2, Aditya,1/22/2014 AM, Update, SALARY,20000,2000 3, Aditya,1/22/2014 AM, Update, SALARY,16000,2000 Now any changes in any field of employees will to be written in aud_emp table.

--Executed before DML statement BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT; --Executed before each row change- : NEW, : OLD are available BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW; --Executed aftereach row change- : NEW, : OLD are available AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW; --Executed after DML statement AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT; END compound_trigger_name; Hopefully this example with make things more clear. SALARY IS NULL) ) THEN v_index := v_index 1; v_emp_changes(v_index).upd_dt := SYSDATE; v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER'); v_emp_changes(v_index).emp_id := : NEW.emp_id; v_emp_changes(v_index).field := 'SALARY'; v_emp_changes(v_index).from_value := to_char(: OLD.

It might be better include the logic in the trigger itself." id="ctl00_m_m_i_ctl00_gr_ctl01_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455169" / The procedure is an autonomous transaction.

create or replace trigger Trig On Test1 BEFORE -- before instead of after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then :new.col3 := 500; -- set value here end if; end;" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455172" / you should do this: create or replace trigger Trig On Test1 BEFORE -- before instead of after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then :new.col3 := 500; -- set value here end if; end; it would be much easier to do it all in a single trigger.

CREATE OR REPLACE TRIGGER emp Before Update BEFORE UPDATE OF location ON emp REFERENCING OLD AS o NEW AS n FOR EACH ROW WHEN (n.salary IS NULL OR n.salary = o.salary) SET n.salary = DECFLOAT(o.salary) / CASE o.location WHEN 'Boston' THEN 1.2 UPDATE emp SET location = 'Boston' WHERE name = 'Jason'; UPDATE emp SET location = 'Toronto' WHERE name = 'Jill'; UPDATE emp SET location = 'New York', salary = 63000 WHERE name = 'John'; SELECT * FROM emp ORDER BY name; NAME RANK LOCATION SALARY ---------- ----------- ---------- ----------- Jack Now, the only problem the above approach has is that our logic is spread across multiple objects.

Every time we re-evaluate the living costs in different cities we need to remember to update both triggers. Certainly what we could do in this case is to store the modifiers in a table and perhaps use a function to encapsulate much of the logic, but then, this is a simple example.

Leave a Reply

  1. dating love sea 26-Dec-2016 23:42

    A "Love Triangle" is when a group of three people are involved in a romantic relationship together.

  2. Naked sex chat online 18-Jan-2017 05:34

    Instant & Private Messaging: You can private message (PM) or "whisper", anyone within the chat.