Home / plentyoffish com online dating / Updating data in a view oracle

Updating data in a view oracle sunnery james and doutzen kroes dating

If you have the Northwind database you can see that it has several views installed by default.The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table.You also cannot reference any pseudo-columns or expressions when you update a view.

updating data in a view oracle-19updating data in a view oracle-41

create or replace trigger update_emp_thru_outemp_view instead of update on outemp referencing new as new begin update emp set ename = :new.ename, empno = :new.empno, job = :new.job, mgr = :new.mgr, hiredate = :new.hiredate, sal = :new.sal, comm = :new.comm, deptno = ( select deptno from dept where dname = :new.dname ) where empno = :old.empno; if ( sql%rowcount = 0 ) then raise_application_error ( -20001, 'Error updating the outemp view !!!

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table.

The information displayed by this view is meaningful only for inherently updatable views.

For a view to be inherently updatable, the following conditions must be met: CREATE TABLE employee ( employee_no VARCHAR2(8), last_name VARCHAR2(25) NOT NULL, first_name VARCHAR2(10) NOT NULL, dept_code VARCHAR2(3) NOT NULL, active_flag VARCHAR2(1) DEFAULT 'Y', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE permission_code ( pcode VARCHAR2(2), pcode_description VARCHAR2(40) NOT NULL, mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_role ( dept_code VARCHAR2(3), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_permission ( employee_no VARCHAR2(8), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE dept_code ( INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('5001', 'Mark', 'Townsend', 'LCR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('3996', 'Dacko', 'Carol', 'ESR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('6842', 'Morgan', 'Daniel', 'ADM', 'Y'); INSERT INTO permission_code VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CL', 'CLASS CODES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CR', 'CREWS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CT', 'CREW TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ADM', 'ADMINISTRATION'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('COO', 'COORDINATOR'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESE', 'ELECTRICAL SERVICE'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESR', 'ELECTRICAL SERVICE REP'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ENG', 'ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('LCR', 'LINE CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('MCR', 'METER CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('NWE', 'NETWORK ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('SKA', 'SKETCH ARTIST'); INSERT INTO user_role (dept_code, pcode, access_level) SELECT r.dept_code, p.pcode, 'R' FROM dept_code r, permission_code p; INSERT INTO user_permission (employee_no, pcode, access_level) SELECT e.employee_no, r.pcode, r.access_level FROM employee e, user_role r WHERE e.dept_code = r.dept_code; COMMIT; CREATE OR REPLACE VIEW role_permission_view AS SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level FROM user_role r, permission_code p WHERE r.pcode = p.pcode; desc role_permission_view col data_type format a15 SELECT column_name, data_type, data_length FROM user_tab_cols WHERE table_name = 'ROLE_PERMISSION_VIEW'; col type format a30 SELECT column_name, , e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description FROM employee e, user_role r, user_permission u, permission_code p WHERE e.dept_code = r.dept_code AND e.employee_no = u.employee_no AND r.pcode = u.pcode AND r.pcode = p.pcode ORDER BY 1,3; desc employee_permission_view SELECT column_name, ON role_permission_view FOR EACH ROW DECLARE x INTEGER; BEGIN SELECT COUNT(*) INTO x FROM permission_code WHERE pcode = : NEW.pcode; IF x = 0 THEN INSERT INTO permission_code (pcode, pcode_description, mod_user_id, mod_user_date) VALUES (: NEW.pcode, 'New Code', USER, SYSDATE); END IF; SELECT COUNT(*) INTO x FROM dept_code WHERE dept_code = : NEW.dept_code; IF x = 0 THEN INSERT INTO dept_code (dept_code, dept_name) VALUES (: NEW.dept_code, 'New Dept'); END IF; INSERT INTO user_role (dept_code, pcode, mod_user_id) VALUES (: NEW.dept_code, : NEW.pcode, 'Morgan'); INSERT INTO test (test) VALUES ('Z'); END ioft_insert_role_perm; / SELECT * FROM permission_code WHERE pcode = 'DM'; SELECT * FROM dept_code WHERE dept_code = 'DAN'; SELECT * FROM user_role WHERE dept_code = 'DAN'; SELECT * FROM test; ON role_permission_view FOR EACH ROW BEGIN UPDATE user_role SET access_level = : NEW.access_level, mod_user_id = USER, mod_user_date = SYSDATE WHERE dept_code = : OLD.dept_code AND permission_code = : OLD.permission_code; END ioft_role_perm; / SELECT trigger_name, trigger_type, action_type, description FROM user_triggers; SELECT * FROM employee_permission_view; UPDATE role_permission_view SET access_level = 'W' WHERE dept_code = 'SKA'; SELECT * FROM employee_permission_view; UPDATE employee_permission SET access_level = 'Z';/* what does it mean to delete LCR from employee_permission_view?

976 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*