Thread: update trigger on a view
06-19-06, 05:03 #1Registered User
- Join Date
- Jun 2006
Unanswered: update trigger on a view
Hi all, I have a problem on an oracle database. Let me explain:
I need to update a table when a certain table is created. The thing is we are not allowed (for business reasons) to create a trigger on the table, so I have to put the trigger on the view.
The trigger is launched when this view is updated manually, so far everything works fine, (i have created an instead-of trigger), but the trigger is not launched when the source table is updated, not even when the view is refreshed. What is the reason?? is there a way to have the trigger be launched when the source tables are updated, even though the trigger is in the view??? any ideas???
the code for the view I have created is:
CREATE OR REPLACE TRIGGER INTEGRACION.NEW_VIEW_TRIGGER INSTEAD OF DELETE OR INSERT OR UPDATE ON INTEGRACION.NEW_VIEW REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO P_PRUEBA_GLOBAL VALUES ( :NEW.IDPR, :NEW.PROJECTNUMBER, :NEW.PROJECTGLOBALTRADENAME, :NEW.PROJECTSHORTNAME, :NEW.PROJECTSTATUSDESCRIPTION, :NEW.PROJECTSTATUSABBREVIATION, :NEW.IDTHEME, :NEW.IDSUBSTANCE, :NEW.IDTH, :NEW.THEMESTATUS, :NEW.THEMENUMBER, :NEW.THEMEDESCRIPTION, :NEW.IDSU, :NEW.SUBSTANCEROCHENUMBER, :NEW.SUBSTANCEGENERICNAME, :NEW.SUBSTANCEDESCRIPTION, :NEW.SUBSTANCERNUMBER, :NEW.SUBSTANCETRADENAME, NULL, P_PRUEBA_GLOBAL_SEQ.NEXTVAL, 0, SYSDATE, 1, NULL, NULL, 'N', -1); END;
06-19-06, 08:59 #2Moderator.
Provided Answers: 1
- Join Date
- Sep 2002
No there isn't. If you need something to be triggered by updates of the source table rather than the view then you need a trigger on the source table. What is the reason? Because and INSTEAD OF trigger fires "instead of" updates to the specified view, period. It would be very wrong and strange if it also fired "as well as" updates on the base table of the view.
BTW, all your trigger does is insert into P_PRUEBA_GLOBAL, surely it also needs to perform the triggering insert/update/delete on the view's base table?Tony Andrews