TRIGGER Instead of

Alexandra Robin

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.

715331

just go to the object browser and change it manually. notice that you can create it only on views.

Alexandra Robin

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;