Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Question 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:

    Code:
    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;
    thank you very much

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

Posting Permissions

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