Create trigger on a view : Trigger on View « Trigger « Oracle PL / SQL

1. Oracle PL / SQL
2. Trigger
3. Trigger on View

Create trigger on a view

 
SQL>
SQL> CREATE TABLE employee(
  2           emp_id           INTEGER,
  3           emp_name         VARCHAR2(32),
  4           supervised_by    INTEGER,
  5           pay_rate         NUMBER(9,2),
  6           pay_type         CHAR);

Table created.

SQL> CREATE TABLE department
  2         (dept_id           INTEGER,
  3          dept_name         VARCHAR2(32));

Table created.

SQL>
SQL> CREATE OR REPLACE VIEW emp_public_data AS
  2      SELECT e.emp_id,
  3             e.emp_name,
  4             d.dept_name
  5      FROM employee e,
  6           department d;

View created.

SQL> CREATE OR REPLACE TRIGGER emp_name_change
  2  INSTEAD OF UPDATE ON emp_public_data
  3  BEGIN
  4      IF (:new.emp_name <> :old.emp_name)
  5      OR (:old.emp_name IS NULL AND :new.emp_name IS NOT NULL) THEN
  6          UPDATE employee
  7          SET emp_name = :new.emp_name
  8          WHERE emp_id = :new.emp_id;
  9      END IF;
 10  END;
 11  /

Trigger created.

SQL>
SQL> drop table employee;

Table dropped.

SQL> drop table department;

Table dropped.

SQL>
SQL> 

 

Related examples in the same category