Sunday, 10 June 2018

OLD and NEW qualifiers

  • The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.
  • The OLD and NEW qualifiers can be used only with row triggers. They cannot be used with statement triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.

Example of using OLD and NEW qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

     INSERT INTO audit_emp_table (user_name, timestamp,
     id, old_last_name, new_last_name, old_title,
     new_title, old_salary, new_salary)
     VALUES (USER, SYSDATE, :OLD.employee_id,
     :OLD.last_name, :NEW.last_name, :OLD.job_id,
     :NEW.job_id, :OLD.salary, :NEW.salary );

END;

No comments:

Post a Comment