Updating table through view in oracle

COUNTRIES group by continent; You can embed a SAS LIBNAME statement or a SAS/ACCESS LIBNAME statement in a view by using the USING LIBNAME clause.

When PROC SQL executes the view, the stored query assigns the libref.

The scope of the libref is local to the view and does not conflict with any identically named librefs in the SAS session.When the query finishes, the libref is disassociated.The connection to the DBMS is terminated and all data in the library becomes unavailable.*/ 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: '

Leave a Reply