TUHRA Logo

Oracle JDeveloper 10g for Forms & PL/SQL Developers
Script
Chapter 15
Section Loading Audit Columns Using a Database Procedure and Application Context | Create the Database Objects
Code Script to create BEFORE INSERT / UPDATE trigger on EMPLOYEES for auditing
Return to listings...

Run the following script from SQL*Plus or a similar SQL worksheet. Connect as HR

CREATE OR REPLACE TRIGGER employees_audit_biu
  BEFORE INSERT OR UPDATE
  ON employees
  FOR EACH ROW
DECLARE
  v_user   VARCHAR2(30);
BEGIN
  v_user := security_pkg.logged_in_user;

  IF INSERTING
  THEN
    :NEW.created_by := v_user;
    :NEW.created_date := SYSDATE;
  ELSIF UPDATING
  THEN
    :NEW.modified_by := v_user;
    :NEW.modified_date := SYSDATE;
  END IF;
END;
/