instead-of trigger

On top of using this blog to speak on data and database design and development, I’ll also be using it as a notebook, documenting the moments of discovery and clarity that occur when dealing with problems. I wish I’d been taking notes this way since I began my career in 1995, instead of just hoping I’d remember the next time I ran into the same problem.

Today I had to learn the hard way (or re-learn; did I learn this before?) that when you replace a view that happens to have an instead-of trigger attached to it, the CREATE OR REPLACE will drop the instead-of trigger silently. CREATE OR REPLACE lulled me into a false sense of security. It seems to imply that the object and its dependencies will not be dropped, but left alone as the source code for the view is replaced in the data dictionary.

Not so.

This “discovery” was found due to things that broke in production. My tested script replaced the view, but silently and viciously removed the important instead-of trigger that belonged to the view.

Lets have a look to be sure I’m not hallucinating:

DROP VIEW my_emp_vw;
DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 90;

CREATE OR REPLACE TRIGGER my_emp_vw_iot
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON my_emp_vw 
  FOR EACH ROW
DECLARE
BEGIN
  IF (inserting) THEN
     INSERT INTO my_emp
        (employee_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date
        ,job_id
        ,salary
        ,commission_pct
        ,manager_id
        ,department_id)
     VALUES
        (:new.employee_id
        ,:new.first_name
        ,:new.last_name
        ,:new.email
        ,:new.phone_number
        ,:new.hire_date
        ,:new.job_id
        ,:new.salary
        ,:new.commission_pct
        ,:new.manager_id
        ,:new.department_id);
     
  ELSIF (updating) THEN
     UPDATE my_emp
        SET first_name = :new.first_name,
            last_name = :new.last_name,
            email = :new.email,
            phone_number = :new.phone_number,
            hire_date = :new.hire_date,
            job_id = :new.job_id,
            salary = :new.salary,
            commission_pct = :new.commission_pct,
            manager_id = :new.manager_id,
            department_id = :new.department_id
      WHERE employee_id = :new.employee_id;
  
  ELSIF (deleting) THEN
     DELETE FROM my_emp
       WHERE employee_id = :old.employee_id;
  END IF;
END my_emp_vw_iot;
/

Now that the objects have been created, let’s look at the state and existence of our objects in the data dictionary.

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW_IOT   TRIGGER
MY_EMP_VW       VIEW
MY_EMP          TABLE

Now lets edit the view in place (in actuality it is dropping the view and re-creating it):

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 50;

And check whether the trigger was maintained after the CREATE OR REPLACE

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW       VIEW
MY_EMP          TABLE

Sure enough. The dependent trigger MY_EMP_VW_IOT was not maintained. It was silently dropped during the REPLACE part of the CREATE OR REPLACE VIEW statement. So be careful. Do better than I. Remember this every time you script a view replacement. The instead-of trigger will have to be re-created.

Live well.