0 Replies Latest reply: Sep 20, 2013 10:35 AM by user12240205 RSS

    How to base a block on "Transactional Triggers" to manipulate join view?

    user12240205

      Forms  Pros, Experts and Gurus,

       

      Your help would be greatly appreciate on this one. Can you please tell us

        (1.) How to base a block based on a Transactional Trigger to manipulate the view V_DEPT_EMP. We want to view the data (COLUMNS) on the view on the block, and

        (2.) Also want to update the V_EMP table's ESTATUS column through the view (i.e. EMPSTATUS col on view).

        (3.) We want to insert a log entry to the V_EMP_LOG table when we change the value of the EMPSTATUS view column. Log entry should state, "Emp Status changed from Y to N" etc.

       

      How do you code the ON-SELECT, ON-FETCH, ON-LOCK AND ON-UPDATE triggers and any others?

       

      Your expert advice would be greatly appreciated.

       

      Thanks in advance.

       

      {code}

      DROP TABLE v_emp;

      DROP TABLE v_dep;

      DROP VIEW v_dept_emp;

      CREATE TABLE v_dep (dcode NUMBER PRIMARY KEY, dname VARCHAR2(20));

      CREATE TABLE v_emp  (ecode NUMBER PRIMARY KEY, ename VARCHAR2(30), estatus VARCHAR2(1) /* Y/N */, dcode NUMBER);

      ALTER TABLE v_emp ADD CONSTRAINT v_emp_2_v_ept_fk FOREIGN KEY (dcode) REFERENCES v_dep (dcode);

      CREATE VIEW v_dept_emp AS SELECT d.dname dept_name, e.ecode empcode, e.ename empname, e.estatus empstatus

        FROM v_dep d, v_emp e WHERE d.dcode = e.dcode;

      INSERT INTO v_dep VALUES (1, 'Dept 1'); 

      INSERT INTO v_dep VALUES (2, 'Dept 2');

      INSERT INTO v_emp VALUES (101, 'Emp 101', 'Y', 2);

      INSERT INTO v_emp VALUES (102, 'Emp 102', 'N', 2);

      CREATE TABLE v_emp_log (log_entry VARCHAR2(50));

      SELECT * FROM v_dept_emp;

      {code}