Oracle trigger if inserting or updating
v_user VARCHAR2(50); --logged in user PROCEDURE flush_logs IS v_updates CONSTANT SIMPLE_INTEGER := v_emp_changes.count(); BEGIN FORALL v_count IN 1..v_updates INSERT INTO aud_emp VALUES v_emp_changes(v_count); v_emp_changes.delete(); v_index := 0; --resetting threshold for next bulk-insert.
END flush_logs; AFTER EACH ROW IS BEGIN IF INSERTING 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).action := 'Create'; v_emp_changes(v_index).field := '*'; v_emp_changes(v_index).from_value := 'NULL'; v_emp_changes(v_index).to_value := '*'; ELSIF UPDATING THEN IF ( (: OLD.
When we retrieve that column, the query does not return the updated column values, but other columns are being updated and we are able to see those values. It happens more often than you might think, in real life.) I looked at their trigger and decided immediately that I did not like it.
A trigger is just like a subroutine , but most people consider triggers part of the DDL.
And just as you would not review a CREATE TABLE statement while reviewing a package implementation, most people do not review the hidden code in a trigger.
This name information is derived data based on the values in two other columns.
The NAME attribute is the result of a trivial function, the function is not computationally expensive, and they could have indexed the function if necessary.
This causes confusion (otherwise, I would not have received the e-mail in the first place! In this system, this NAME attribute was apparently not expected to always be “fname||’ ‘||lname”; otherwise, this update would never have been attempted (because it does not set the NAME attribute to that function result! Apparently they were trying really hard to set it to some other value (but they could not do so without first disabling the trigger). Triggers are hidden in the data definition language (DDL) and in the schema.