Forum Stats

  • 3,853,631 Users
  • 2,264,247 Discussions
  • 7,905,419 Comments

Discussions

TRIGGER Instead of

Alexandra Robin
Alexandra Robin Member Posts: 1,676 Blue Ribbon
edited Sep 2, 2009 4:01PM in APEX Discussions
How do I use the wizard in Apex to create an "instead of " trigger? Or where can I write one manually? The wizard adds it's own 'create" statement and more so that it gets all messed up!
Thank you.
Tagged:

Answers

  • 715331
    715331 Member Posts: 92
    just go to the object browser and change it manually. notice that you can create it only on views.
  • Alexandra Robin
    Alexandra Robin Member Posts: 1,676 Blue Ribbon
    Thanks.

    For all my questions to the forum, I figured out this ANSWER after loads of laboring and it worked...ah yes, there really is SATISFACTION! Here you go:

    Go to the SQL Workshop > SQL Commands button. After you write the code, run it so it will get saved in the object browser.

    1. write an INSTEAD OF trigger that will update the tables in your view INSTEAD OF the view itself. It should look someting like this:

    create or replace trigger SH_VEND_ITEM_VW_UPD
    instead of UPDATE ON ITEM_VW
    for each row
    begin

    UPDATE ACCESS_ITEM
    SET ITEM_NOTES = :new.ITEM_NOTES
    WHERE ITEMNO = :old.ITEMNO;

    UPDATE ACCESS_VENDOR
    SET VEND_NOTES = :new.VEND_NOTES
    WHERE VENDNO = :old.VENDNO;

    UPDATE VEND_ITEM_PRIORITY
    SET VEND_ITEM_NOTES = :new.VEND_ITEM_NOTES
    WHERE VI_PRIORITY_ID = :old.VI_PRIORITY_ID;

    end;

    2. go to your application and create a PL/SQL page process, AFTER SUBMIT that looks like it's going to update the view itself
    UPDATE VEND_ITEM_VW
    SET ITEM_NOTES = :P3_ITEM_NOTES,
    VEND_NOTES = :P3_VEND_NOTES,
    VEND_ITEM_NOTES = :P3_VEND_ITEM_NOTES
    WHERE VI_PRIORITY_ID = :P3_VI_PRIORITY_ID;
This discussion has been closed.