Forum Stats

  • 3,874,667 Users
  • 2,266,763 Discussions
  • 7,911,935 Comments

Discussions

Need help with INSTEAD OF trigger on view

user12238815
user12238815 Member Posts: 4
edited Nov 18, 2013 6:49AM in SQL & PL/SQL

Hi,

I am trying to use INSTEAD OF on a view because I will be updating the calling table in my trigger, which will cause mutation. 

I need to update attribute7 of another record based on the new attribute7 of the current record (record being updated in the form).  Below is my script.  My problem is it does not perform the update. 

-----------------------------------------------------------------------------------------------------------------------------------

create or replace view xxont_oe_order_lines_vw as select * from oe_order_lines_all;

CREATE OR REPLACE TRIGGER APPS.xxont_sync_ard

instead of insert or update on xxont_oe_order_lines_vw

referencing

new as new

old as old

for each row

begin

   update oe_order_lines_all

       set attribute7 = :new.attribute7

   where attribute18 = to_char(:new.header_id)

      and attribute17 = to_char(:new.line_id)

      and flow_status_code <> 'CANCELLED' ;      

     

  /*exception

  when others then

     null ;

  end ;*/        

end ;

/

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,757 Red Diamond
    edited Nov 18, 2013 6:31AM

    Hi,

    Your code is working as expected for me.

    Post a complete test case, so that people can re-create the exact problem you're having.  Include a CREATE TABLE statement for the base table, a few INSERT and UPDATE statements on the view, and what the table contain after each one.

    If the trigger is working, rows INSERTed into the view will not INSERT anything, since there is no INSERT statement in the trigger.  Remember, an "INSTEAD OF" trigger is just what the name says; instead of performing the DML you specified, the trigger is fired.  You may still get messages such as "1 row created", but that just means the statement completed, and the trigger did what is was told to do.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond

    Are you sure a record exists for the criteria used in the query?

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Always make your code developer friendly. Do extensive code instrumentation. At lease you can do this.

    create or replace trigger apps.xxont_sync_ard
    instead of insert or update on xxont_oe_order_lines_vw
    referencing
    new as new
    old as old
    for each row
    declare
       zero_update exception;
    begin
       update oe_order_lines_all
          set attribute7 = :new.attribute7
        where attribute18 = to_char(:new.header_id)
          and attribute17 = to_char(:new.line_id)
          and flow_status_code <> 'CANCELLED' ;

          

       if sql%rowcount = 0 then
         raise zero_update;
       end if;

    exception
      when zero_update then
         raise_application_error
         (
               -20001
             , 'OE_ORDER_LINES_ALL: 0 rows updated attribute18 = "'
               || attribute18
               || '" attribute17 = "'
               || to_char(:new.line_id)
               || '"'
         );
    end ;

    This will help you raise error when the update does not update any row. This is just an example, make sure if this is what your requirement wants if you want to implement it. If you don't want to raise error then you can just log it in a log table or log file.

This discussion has been closed.