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.
Let’s have a look to be sure I’m not hallucinating:
1 DROP VIEW my_emp_vw;
2 DROP TABLE my_emp;
3
4 CREATE TABLE my_emp AS SELECT * FROM hr.employees;
5
6 CREATE OR REPLACE VIEW my_emp_vw
7 AS
8 SELECT * FROM my_emp WHERE my_emp.department_id = 90;
9
10 CREATE OR REPLACE TRIGGER my_emp_vw_iot
11 INSTEAD OF INSERT OR UPDATE OR DELETE
12 ON my_emp_vw
13 FOR EACH ROW
14 DECLARE
15 BEGIN
16 IF (inserting) THEN
17 INSERT INTO my_emp
18 (employee_id
19 ,first_name
20 ,last_name
21 ,email
22 ,phone_number
23 ,hire_date
24 ,job_id
25 ,salary
26 ,commission_pct
27 ,manager_id
28 ,department_id)
29 VALUES
30 (:new.employee_id
31 ,:new.first_name
32 ,:new.last_name
33 ,:new.email
34 ,:new.phone_number
35 ,:new.hire_date
36 ,:new.job_id
37 ,:new.salary
38 ,:new.commission_pct
39 ,:new.manager_id
40 ,:new.department_id);
41
42 ELSIF (updating) THEN
43 UPDATE my_emp
44 SET first_name = :new.first_name,
45 last_name = :new.last_name,
46 email = :new.email,
47 phone_number = :new.phone_number,
48 hire_date = :new.hire_date,
49 job_id = :new.job_id,
50 salary = :new.salary,
51 commission_pct = :new.commission_pct,
52 manager_id = :new.manager_id,
53 department_id = :new.department_id
54 WHERE employee_id = :new.employee_id;
55
56 ELSIF (deleting) THEN
57 DELETE FROM my_emp
58 WHERE employee_id = :old.employee_id;
59 END IF;
60 END my_emp_vw_iot;
61 /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 TABLEAnd 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 TABLESure 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(s) will have to be re-created.
Until we meet again to ascend the database mountain!