1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

TRIGER INVALID ora-04098

Discussion in 'General' started by monkey, Apr 23, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I wrote this in one sql file:

    Code (Text):


    CREATE TRIGGER t1 after UPDATE OF surname ON m WHEN (OLD.idm=1)
    BEGIN
    UPDATE m
    NEW.mname='trigger1';
    END t1;
     
     

    and got: TRIGGER t1 compiled
    Warning: execution completed with warning

    Then I wrote this to other sql file:

    Code (Text):


    UPDATE m
    SET msurname='SURNAME_UPDATED'
    WHERE imd=1;
    and when running that got :

    trigger t1 is invalid


    PLEASE, why is t1 INVALID?
    Where is the mistake?
    Many thanks!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Please, before I think for ages how to solve it, do U see any idea how to manage it?

    many thanks!!!

    What do I have to do, if I have one table and want to one of the names to be updated after updating on some surname?


    Many thanks!!!
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    possible solutions : Write information on changes in other table or to use instead-trigger
     
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Does it mean, trigger like making one update in table on certain place as consequence of another update on another certain place in the same table is not possible in oracle?

    Do I need to create another table with approprirate values?
    many thanks!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're going about this in the wrong way -- you cannot use an after update trigger if you want to modify values in the updated record:


    Code (SQL):

    SQL> CREATE TABLE m(
      2          idm     NUMBER,
      3          mname   varchar2(40),
      4          msurname varchar2(40));


    TABLE created.


    SQL>
    SQL> CREATE TRIGGER t1 BEFORE UPDATE OF msurname ON m
      2  FOR each ROW
      3  BEGIN
      4          IF :OLD.idm = 1 THEN
      5                  :NEW.mname:='trigger1';
      6          END IF;
      7  END t1;
      8  /


    TRIGGER created.


    SQL>
    SQL> SHOW errors



    No errors.



    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO m
      4                  VALUES(i, 'Borpo', 'Fnermgrinkle');
      5          END loop;
      6
      7          commit;
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT *
      2  FROM m
      3  WHERE idm=1;


           IDM MNAME                                    MSURNAME
    ---------- ---------------------------------------- ----------------------------------------
             1 Borpo                                    Fnermgrinkle


    SQL>
    SQL> UPDATE m
      2  SET msurname='SURNAME_UPDATED'
      3  WHERE idm=1;


    1 ROW updated.


    SQL>
    SQL> SELECT *
      2  FROM m
      3  WHERE idm=1;


           IDM MNAME                                    MSURNAME
    ---------- ---------------------------------------- ----------------------------------------
             1 trigger1                                 SURNAME_UPDATED


    SQL>
     

    Notice it's a BEFORE UPDATE trigger that is used.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    If it is a table level trigger when u should remove when condition.
    in update u should use SET command
    and remove new in 4th line.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've missed the point entirely -- he's trying to write an AFTER UPDATE trigger then trying to update values in the :new block AFTER the changes have been applied. Please see my post regarding this.