*/ ON employee_permission_view FOR EACH ROW BEGIN DELETE FROM dept_code WHERE dept_code = : OLD.dept_code; UPDATE employee SET dept_code = NULL, mod_user_id = USER, mod_user_date = SYSDATE WHERE dept_code = : OLD.dept_code; DELETE FROM test WHERE test = 'Z'; END ioft_emp_perm; / SELECT * FROM employee_permission_view; DELETE FROM employee_permission_view WHERE dept_code = 'LCR'; desc employee DELETE FROM employee_permission_view WHERE dept_code = 'LCR'; conn scott/tiger CREATE OR REPLACE TYPE emp_type AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7, 2), comm NUMBER(7, 2)); / CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type; / CREATE OR REPLACE TYPE dept_type AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), emps emp_tab_type); / CREATE OR REPLACE VIEW dept_or OF dept_type WITH OBJECT IDENTIFIER (deptno) AS SELECT deptno, dname, loc, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm FROM emp WHERE emp.deptno = dept.deptno) AS emp_tab_type) FROM dept; / CREATE OR REPLACE TRIGGER dept_emplist_tr INSTEAD OF UPDATE ON NESTED TABLE emps OF dept_or REFERENCING NEW AS NEW FOR EACH ROW BEGIN dbms_output.put_line('New: '
This course introduces students to the fundamentals of SQL using Oracle Database 11g database technology.
Notes on Updatable Views An updatable view is one you can use to insert, update, or delete base table rows.
You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.
Suppose you are working on someone else’s project, because you have inherited someone else’s work. When you run an update and see “1 row processed,” 500 other things may also have happened.
Say you have a bit of code or better yet a stored procedure that represents a transaction, and you read it. I get it.” But if you are on a system laden with triggers all over the place, you won’t have gotten it at all—you’ll have gotten it wrong, at best. More than once, I’ve received an e-mail similar to the following (this is a cut-and-paste—I did not make this up): We have a problem when updating a column.