How to use if updating on nested column in triggers

6854933580_2c8b688306_z

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.Trigger automatically associated with DML statement, when DML statement execute trigger implicitly execute.You can create trigger using the CREATE TRIGGER statement.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?

DDL triggers execute in response to a variety of data definition language (DDL) events.A trigger is triggered automatically when an associated DML statement is executed.For Example: The price of a product changes constantly.DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.DML events are INSERT, UPDATE, or DELETE statements on a table or view.If trigger activated, implicitly fire DML statement and if trigger deactivated can't fire.

You must have an account to comment. Please register or login here!