Useful shortcuts for vi editor

Wednesday, 10 May 2017

e.g. OLD and NEW Pseudorecords

e.g.
SQL> 
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);

SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
   BEFORE UPDATE
   ON HR.EMPLOYEES
   REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
   FOR EACH ROW
DECLARE
BEGIN
   IF :OLD_ROW.MANAGER_ID IS NOT NULL
   THEN
      INSERT INTO employees_salary_log
           VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
      NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager