Results 1 to 2 of 2

Thread: Oracle - Using an INSTEAD OF TRIGGER

  1. #1

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Oracle - Using an INSTEAD OF TRIGGER

    Two tables that store information about employees.
    Code:
    CREATE TABLE tblEmployee
           (employee_no varchar2(10) NOT NULL, f_name varchar2(30), l_name varchar2(30), dob date, ssn varchar2(12));
           
    CREATE TABLE tblAddress
           (employee_no varchar2(10), address1 varchar2(20), 
                        address2 varchar2(20), city varchar2(10), 
                        state varchar2(2), zip varchar2(7),
                        effective_from date NOT NULL, effective_to date);
    
    --Random Sample Data in the base tables
    insert into tblEmployee values('JCASH', 'Johnny','Cash','12-Feb-1930', '184-12-3456');                 
    
    insert into tblAddress values('JCASH','25 Fir St',NULL,'Memphis','TN','02345','01-Feb-1950','04-Apr-1970');
    insert into tblAddress values('JCASH','25 Hollywood Ave',NULL,'Los Angel','CA','07345','04-Apr-1970',NULL);
    View that allows users to see employee information.

    Code:
    CREATE OR REPLACE VIEW vw_EMPLOYEE_INFO
           AS
              SELECT tE.employee_no, tE.f_name,  tE.l_name, to_char(tE.dob,'mm/dd') dob,
                       tA.address1, tA.address2, ta.city, tA.state, ta.zip, ta.effective_from,
                       ta.effective_to
                     FROM tblEmployee tE, tblAddress tA
                     WHERE tE.employee_no = tA.employee_no(+);
    Code:
    'selecting columns from the view.
    select * from vw_Employee_INFO
    Any attempts to update columns in this view will be met with this error message.

    Code:
    update vw_Employee_Info
    set city = 'Sun City'
    where employee_no = 'JCASH'
    and city = 'Los Angel'
    
    ORA-01779: cannot modify a column which maps to a non key-preserved table.
    To avoid this predicament, you can create an instead of view.

    Code:
    CREATE OR REPLACE TRIGGER ioft_vw_emp
    INSTEAD OF UPDATE
    ON  vw_EMPLOYEE_INFO
    FOR EACH ROW
    BEGIN
      UPDATE tblAddress
      SET address1 = :NEW.address1,
          address2 = :NEW.address2,
          city = :NEW.city,
          state = :NEW.state,
          zip = :NEW.zip,
          effective_from = :NEW.effective_From,
          effective_to = :NEW.effective_to
      WHERE employee_no = :OLD.employee_no;
    
      UPDATE tblEmployee
          SET f_name = :NEW.F_NAME,
          l_name = :NEW.l_name
            WHERE employee_no = :OLD.employee_no;
    END ioft_vw_emp;
    The above update code will now work, thanks to this instead of trigger.
    Code:
    update vw_Employee_Info
    set city = 'Sun City'
    where employee_no = 'JCASH'
    and city = 'Los Angel'
    Tested on Oracle 10G for windows.


    More later.
    Last edited by abhijit; Apr 17th, 2009 at 03:33 PM. Reason: Correct thread title.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  2. #2

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: Oracle - Using an INSTEAD OF TRIGGER

    You can also use an INSTEAD of trigger to insert new records from a view.

    Using the same example in the above post, if you attempt to insert a record.
    Code:
    INSERT INTO vw_Employee_Info(employee_no, address1, address2, city, state, zip, effective_from) 
           values('JCASH','100 Swede Rd','Apt-101','Riverdale','CA','09851',trunc(sysdate));
    
    ORA-01779: cannot modify a column which maps to a non key-preserved table.
    To prevent this error from occurring, introduce the following code.
    Code:
    CREATE OR REPLACE TRIGGER ioft_vw_emp_ins
    INSTEAD OF INSERT
    ON  vw_EMPLOYEE_INFO
    FOR EACH ROW
    BEGIN
      INSERT INTO tblAddress values(:NEW.employee_no,
      :NEW.address1,
      :NEW.address2,
      :NEW.city,
      :NEW.state,
      :NEW.zip,
      :NEW.effective_From,
      :NEW.effective_to);
    END ioft_vw_emp_ins;
    This will now allow you to insert records in the child table.

    You could also have a trigger to insert records in the main table, but that will require adding some extra logic.

    Tested on Oracle 10G running on windows.


    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width