Trigger for View

1) Trigger for View
1@@@@ basic case
referecing PLSQL Design 5th

  It is the most easy case, you could insert value into a view
  without considering CONSTRAINT and JOIN
    TEST@ocm> !cat test.sql
    DROP TABLE t_emp
    /
    CREATE TABLE t_emp (salary NUMBER, name VARCHAR2(20) )
    /
    CREATE OR REPLACE VIEW v_emp_rw
    AS
      SELECT salary FROM t_emp
    /
    CREATE OR REPLACE VIEW v_emp_ro
    AS
      SELECT salary FROM t_emp
    WITH READ ONLY
    /
    BEGIN
      INSERT INTO v_emp_rw VALUES(777);
    END;
    /
    prompt
    prompt @@@ You could not insert value into a read only view
    BEGIN
      INSERT INTO v_emp_ro VALUES(444);
    END;
    /
    SELECT * FROM v_emp_rw
    /
    SELECT * FROM v_emp_ro
    /
    prompt
    prompt @@@ It is possiable to insert values to a common view
    prompt @@@ which is read write without with read only.
    SELECT * FROM t_emp
    /
   
   @@@Now, Test the rw and ro view.
    TEST@ocm> @test.sql
    Table dropped.
    Table created.
    View created.
    View created.
    PL/SQL procedure successfully completed.
   
    @@@ You could not insert value into a read only view
    BEGIN
    *
    ERROR at line 1:
    ORA-01733: virtual column not allowed here
    ORA-06512: at line 2
   
        SALARY
    ----------
           777
   
        SALARY
    ----------
           777
   
    @@@ It is possiable to insert values to a common view
    @@@ which is read write without with read only.
   
        SALARY NAME
    ---------- --------------------
           777



2@@@@ complex case
 it is a join of multiple tables
  @@@Example: could not modify more than one base table
            though a join view.
    TEST@ocm> !cat test.sql
    DROP TABLE ta
    /
    DROP TABLE tb
    /
    CREATE TABLE tb
        (  b_id   NUMBER CONSTRAINT pk_b_id PRIMARY KEY
         , salary NUMBER )
    /
    CREATE TABLE ta
        (  a_id NUMBER 
         , name VARCHAR2(20)
             , b_id NUMBER
         , CONSTRAINT fk_b_id FOREIGN KEY (b_id)
             REFERENCING tb(b_id)  )
    /
    CREATE OR REPLACE VIEW v_ab
    AS
      SELECT a.name , b.salary
        FROM ta a , tb b
       WHERE a.b_id = b.b_id;
    /
    BEGIN
      INSERT INTO v_ab VALUES('Steven','Jack');
    END;
    /
    SELECT * FROM v_ab
    /
    SELECT * FROM ta
    /
    SELECT * FROM tb
    /
    TEST@ocm> @test.sql
    Table dropped.
    Table dropped.
    Table created.
    Table created.
    View created.
    View created.
    BEGIN
    *
    ERROR at line 1:
    ORA-01776: cannot modify more than one base table through a join view
    ORA-06512: at line 2
    no rows selected
    no rows selected
    no rows selected



3@@@@ Create trigger for complex view
INSTEAD OF Triggers
INSTEAD OF triggers control insert, update, merge, and delete operations on views,
not tables. They can be used to make nonupdateable views updateable and to override
the default behavior of views that are updateable.
To create (or replace) an INSTEAD OF trigger, use the syntax shown here:
    1 CREATE [OR REPLACE] TRIGGER trigger_name
    2   INTEAD OF operation
    3   ON view_name
    4   FOR EACH ROW
    5 BEGIN
    6    ...code goes here...
    7 END;
Note:
  This is where we see differences between INSTEAD OF triggers and
  other types of triggers. Because INSTEAD OF triggers are not really triggered
  by an event, I don not need to specify AFTER or BEFORE or provide an
  event name. What I do specify is the operation that the trigger is to fire in
  place of (or instead of). Stating INSTEAD OF followed by one of
  INSERT, UPDATE, MERGE, or DELETE accomplishes this.

    @@@Example: Create three table, and one table reference others, then create view
                from equal-join SELECT statement.
    HR@ocm> !cat tmp.sql
    DROP TABLE delivery
    /
    DROP TABLE driver
    /
    DROP TABLE area
    /
    CREATE TABLE area
        (  area_id    NUMBER
         , area_desc  VARCHAR2(30) CONSTRAINT u_area_desc UNIQUE
         , CONSTRAINT pk_area_id PRIMARY KEY (area_id, area_desc) )
    /
    CREATE TABLE driver
        (  driver_id   NUMBER
         , driver_name VARCHAR2(30) CONSTRAINT u_driver_name UNIQUE
         , CONSTRAINT pk_driver_id PRIMARY KEY (driver_id, driver_name) )
    /
    CREATE TABLE delivery
        (  delivery_id    NUMBER
         , delivery_start DATE
         , delivery_end   DATE
         , area_id        NUMBER
         , driver_id      NUMBER
         , CONSTRAINT pk_delivery_id
                  PRIMARY KEY (delivery_id)
         , CONSTRAINT fk_area_id  
                  FOREIGN KEY (area_id) REFERENCING area(area_id) 
         , CONSTRAINT fk_driver_id
              FOREIGN KEY (driver_id) REFERENCING driver(driver_id)  )
    /
    DROP SEQUENCE delivery_id_seq
    /
    DROP SEQUENCE area_id_seq
    /
    DROP SEQUENCE driver_id_seq
    /
    CREATE SEQUENCE delivery_id_seq
    /
    CREATE SEQUENCE area_id_seq
    /
    CREATE SEQUENCE driver_id_seq
    /
    CREATE OR REPLACE VIEW delivery_info
    AS
    SELECT    d.delivery_id
        , d.delivery_start
        , d.delivery_end
        , a.area_desc
        , dr.driver_name
    FROM   delivery  d
         , area      a
         , driver    dr
    WHERE  a.area_id = d.area_id
      AND  dr.driver_id = d.driver_id
    /
   
    HR@ocm> @tmp.sql
    Table dropped.
    Table dropped.
    Table dropped.
    Table created.
    Table created.
    Table created.
    Sequence dropped.
    Sequence dropped.
    Sequence dropped.
    Sequence created.
    Sequence created.
    Sequence created.
    View created.
   
  Because my system relies heavily on this view for query functionality, why not make it
  available for insert, update, and delete as well? I cannot directly issue DML statements
  against the view; it is a join of multiple tables. How would the database know what to
  do with an INSERT ? In fact, I need to tell the database very explicitly what to do when
  an insert, update, or delete operation occurs against the delivery_info view; in other
  words, I need to tell it what to do instead of trying to insert, update, or delete. Thus, I
  will use INSTEAD OF triggers. Let us start with the INSERT trigger.


My INSERT trigger will perform four basic operations:
 ~ Ensure that the delivery_end value is NULL. All delivery completions must be done
   via an update.
 ~ Try to find the driver ID based on the name provided. If the name cannot be found,
   then assign a new ID and create a driver entry using the name and the new ID.
 ~ Try to find the area ID based on the name provided. If the name cannot be found,
   then assign a new ID and create an area entry using the name and the new ID.
 ~ Create an entry in the delivery table.

Bear in mind that this example is intended to demonstrate triggers????ot how to effectively
build a business system! After a while I will probably wind up with a multitude
of duplicate driver and area entries. However, using this view speeds things up by not
requiring drivers and areas to be predefined, and in the fast-paced world of pizza delivery,
time is money!

   @@@Example01_insert: FOR a complex view with join, use trigger to implement DML.
    HR@ocm> !cat tmp.sql
    CREATE OR REPLACE TRIGGER delivery_info_insert
      INSTEAD OF INSERT
      ON delivery_info
    DECLARE
            /* Assure both driver_id and driver_name are primary key
               area_id and area_desc are primary key, at lesast unique*/
        --cursor to get the driver ID by :new.driver_name
        CURSOR curs_get_driver_id ( cp_driver_name_in IN VARCHAR2 )
        IS
         SELECT driver_id
           FROM driver
          WHERE driver_name = cp_driver_name_in;
        l_driver_id  NUMBER;
   
        --cursor to get the area ID by :new.area_desc
        CURSOR curs_get_area_id ( cp_area_desc_in  IN VARCHAR2 )
        IS
         SELECT area_id
           FROM area
          WHERE area_desc = cp_area_desc_in;
        l_area_id  NUMBER;
    BEGIN
       /* busniess logic: Make sure the delivery_end value is NULL */
       IF :new.delivery_end IS NOT NULL THEN
          RAISE_APPLICATION_ERROR( -20000,
          'Delivery end date value must be NULL when delivery createed');
       END IF;
   
       /* program_logic: use sequence to pad driver(driver_id)  */
       OPEN  curs_get_driver_id (  UPPER(:new.driver_name) );
       FETCH curs_get_driver_id INTO l_driver_id;
       IF curs_get_driver_id%NOTFOUND THEN
          SELECT driver_id_seq.nextval
            INTO l_driver_id
            FROM DUAL;
          INSERT INTO driver ( driver_id, driver_name )
               VALUES ( l_driver_id, UPPER(:new.driver_name) );
       END IF;
       CLOSE curs_get_driver_id;
     
       /* program_logic: use sequence to pad area(area_id)*/
       OPEN  curs_get_area_id(  UPPER(:new.driver_name)  );
       FETCH curs_get_area_id INTO l_area_id;
       IF curs_get_area_id%NOTFOUND THEN
          SELECT area_id_seq.nextval
            INTO l_area_id
            FROM DUAL;
          INSERT INTO area ( area_id, area_desc )
               VALUES ( l_area_id, UPPER(:new.area_desc) );
       END IF;
       CLOSE curs_get_area_id;
   
       INSERT INTO delivery
        (  delivery_id
         , delivery_start
         , delivery_end
         , area_id
         , driver_id )
       VALUES
        (  delivery_id_seq.nextval
         , NVL(:NEW.delivery_start, SYSDATE)
         , NULL
         , l_area_id
         , l_driver_id );
   
    END;
    /
   
    HR@ocm> @tmp.sql
   
    Trigger created.
   
   @@@Now test the trigger:
    HR@ocm> !cat tmpx.sql
    BEGIN
       INSERT INTO delivery_info
        (  delivery_id
         , delivery_start
         , delivery_end
         , area_desc
         , driver_name )
       VALUES
        (NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED' );
    END;
    /
    HR@ocm> @tmpx.sql
    PL/SQL procedure successfully completed.
    HR@ocm> SELECT * FROM delivery;
    DELIVERY_ID DELIVERY_START    DELIVERY_END           AREA_ID    DRIVER_ID
    ----------- ------------------- ------------------- ---------- ----------
          2 2013-01-05 15:52:43                  2        2


   @@@Example01_update:
    HR@ocm> !cat tmp.sql
    CREATE OR REPLACE TRIGGER delivery_info_update
      INSTEAD OF UPDATE
      ON delivery_info
    DECLARE
       --cursor to get the delivery entry
       CURSOR curs_get_delivery ( cp_delivery_id_in  IN  NUMBER )
       IS
        SELECT delivery_end
          FROM delivery
         WHERE delivery_id = cp_delivery_id_in
       FOR UPDATE OF delivery_end NOWAIT; --with CURRENT OF
       l_delivery_end DATE;
    BEGIN
       OPEN  curs_get_delivery(:NEW.delivery_id);
       FETCH curs_get_delivery INTO l_delivery_end;
       IF l_delivery_end IS NOT NULL THEN
          RAISE_APPLICATION_ERROR( -20000,
             'The delivery end date has already been set.');
       ELSE
          UPDATE delivery
             SET delivery_end = :new.delivery_end
           WHERE CURRENT OF curs_get_delivery;
       END IF;
       CLOSE curs_get_delivery;
    END;
    /
    HR@ocm> @tmp.sql
    Trigger created.
   
    HR@ocm> !cat test.sql
    BEGIN
       UPDATE delivery_info
          SET delivery_end = SYSDATE + 1
        WHERE driver_name = 'BIG TED';
    END;
    /
   
    HR@ocm> @test.sql
    PL/SQL procedure successfully completed.
   
    HR@ocm> SELECT delivery_end FROM delivery_info;
    DELIVERY_END
    -------------------
    2013-01-06 16:08:28
   
    HR@ocm> @test.sql
    BEGIN
    *
    ERROR at line 1:
    ORA-20000: The delivery end date has already been set.
    ORA-06512: at "HR.DELIVERY_INFO_UPDATE", line 15
    ORA-04088: error during execution of trigger 'HR.DELIVERY_INFO_UPDATE'
    ORA-06512: at line 2


    Note: without FOR UPDATE keyword would occur errors below:
    HR@ocm> show error;
    Errors for TRIGGER DELIVERY_INFO_UPDATE:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    18/7     PL/SQL: SQL Statement ignored
    20/25     PL/SQL: ORA-00904: : invalid identifier
    20/25     PLS-00404: cursor 'CURS_GET_DELIVERY' must be declared with FOR
             UPDATE to use with CURRENT OF


   @@@Example01_delete: Obviously, it could not be deleted
    HR@ocm> !cat tmp.sql
    CREATE OR REPLACE TRIGGER delivery_info_delete
      INSTEAD OF DELETE
      ON delivery_info
    BEGIN
       -- business logic
       IF :new.delivery_end IS NOT NULL THEN
          RAISE_APPLICATION_ERROR(-20000,
           'Completed deliveries cannot be deleted');
       END IF;
       DELETE delivery
       WHERE delivery_id = :new.delivery_id;
    END;
    /
    HR@ocm> @tmp.sql
    Trigger created.
    HR@ocm> DELETE delivery_info WHERE delivery_id = 2;
    1 row deleted.
   
    HR@ocm> SELECT * FROM delivery;
    DELIVERY_ID DELIVERY_START    DELIVERY_END           AREA_ID    DRIVER_ID
    ----------- ------------------- ------------------- ---------- ----------
          2 2013-01-05 15:52:43 2013-01-06 16:08:28         2        2