DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: table error

  1. #1
    Join Date
    Dec 2007
    Posts
    4

    table error

    hi,
    REATE TABLE T_RE_SEQU
    (
    SCHEMA_NAME VARCHAR2(60 CHAR),
    SEQUENCE_NAME VARCHAR2(60 CHAR),
    NB_INITIAL_VALUE NUMBER(38),
    NB_CURRENT_VALUE NUMBER(38),
    NB_INCREMENT_VALUE NUMBER(38)
    )
    Last edited by sushant; 03-17-2008 at 07:49 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    This should work. You needed to specify for each row in order to access the :NEW. and :OLD. metarows. If you wanted to update a different row in the table during an update, delete or insert, then you would need to insert into a temporary table and have an after statement level trigger fire and read the temporary table and that would be able to update the table without a mutating table error. There are four basic type of triggers there are two before triggers and two after triggers comprised of statement level and row level triggers.

    Code:
    CREATE OR REPLACE TRIGGER TR_REF_SEQUENCE_UPDATE_DATE
       BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
          FOR EACH ROW
    DECLARE
    BEGIN
       :NEW.DT_LAST_UPDATED = SYSDATE;
    END TR_REF_SEQUENCE_UPDATE_DATE;
    /

  3. #3
    Join Date
    Dec 2007
    Posts
    4

    Post

    hi gandolf989,
    Thanks for your reply.this is a good suggestion to use before
    trigger to acess :new variables. i have already tried this but as
    i mentioned in origional thread i'm getting ORA-00036: maximum
    number of recursive SQL levels (50) exceeded Error.
    please look at the code bellow.

    CREATE OR REPLACE PACKAGE REF_SEQ_PKG
    AS
    TYPE TYPE_SCHEMA IS TABLE OF U_REF.T_REF_SEQUENCE.VC_SCHEMA_NAME%TYPE INDEX BY BINARY_INTEGER;

    TYPE TYPE_SEQUENCE IS TABLE OF U_REF.T_REF_SEQUENCE.VC_SEQUENCE_NAME%TYPE INDEX BY BINARY_INTEGER;


    TYPE_SCHEMA_VAR TYPE_SCHEMA ;
    TYPE_SCHEMA_EMPTY TYPE_SCHEMA;


    TYPE_SEQUENCE_VAR TYPE_SCHEMA ;
    TYPE_SEQUENCE_EMPTY TYPE_SCHEMA;

    END;
    /


    CREATE OR REPLACE TRIGGER TR_REF_SEQ_PKG
    BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
    BEGIN

    REF_SEQ_PKG.TYPE_SCHEMA_VAR :=
    REF_SEQ_PKG.TYPE_SCHEMA_EMPTY ;

    REF_SEQ_PKG.TYPE_SEQUENCE_VAR :=
    REF_SEQ_PKG.TYPE_SEQUENCE_EMPTY;
    END;
    /

    CREATE OR REPLACE TRIGGER TR_REF_SEQ_PKG_BI_ROW
    BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
    FOR EACH ROW
    BEGIN


    REF_SEQ_PKG.TYPE_SCHEMA_VAR(REF_SEQ_PKG.TYPE_SCHEMA_VAR.COUNT +1) := :NEW.VC_SCHEMA_NAME ;

    REF_SEQ_PKG.TYPE_SEQUENCE_VAR(REF_SEQ_PKG.TYPE_SEQUENCE_VAR.COUNT + 1) := :NEW.VC_SEQUENCE_NAME ;


    END;
    /


    CREATE OR REPLACE TRIGGER TR_T_REF_SEQUENCE_AI
    AFTER INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE

    BEGIN

    DBMS_OUTPUT.PUT_LINE(3);
    FOR i IN 1 .. REF_SEQ_PKG.TYPE_SCHEMA_VAR.COUNT
    LOOP

    UPDATE T_REF_SEQUENCE T1
    SET DT_LAST_UPDATED = SYSDATE
    WHERE EXISTS (SELECT 1
    FROM DUAL
    WHERE t1.VC_SCHEMA_NAME = REF_SEQ_PKG.TYPE_SCHEMA_VAR(i)
    AND t1.VC_SEQUENCE_NAME = REF_SEQ_PKG.TYPE_SEQUENCE_VAR(i) );

    END LOOP;

    END;
    /

    when i try to insert into the same table it gives ORA-00036: maximum
    number of recursive SQL levels (50) exceeded Error.
    This is because there is recursive call to the trigger.
    do you find any bug in code. how to overcome this error?
    please suggest any solution.

    THANKS
    sushant

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Code:
    declare
      v_understood := false;
    begin
      while v_understood == false loop
         do read-Galdolf989-post
         if understood then 
            v_understood := true;
         end if; 
      end loop;
      do fix-your-code;
    end;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Sushant, spend some time reading PL/SQL Best Practices by Steven Feuerstein. It's a short book, but it should help you to understand the PL/SQL language.

    Once you do that, writing triggers should be much easier.

  6. #6
    Join Date
    Dec 2007
    Posts
    4
    hi gandolf989,
    Thanks for your valuable reply. i will work on it.
    can you send me the link of this book ?



    Thanks
    sushant

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm wondering if the next thing you are gonna do is asking gandolf989 to read it for you. Don't be that lazy and just google "PL/SQL Best Practices by Steven Feuerstein"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Dec 2007
    Posts
    4
    you have taken it on wrong way. i was just not sure that this Book is
    available for all user or not. thats why i requested to share the link only.
    i regret for the inconvenience.
    Thanks for your help

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by sushant
    you have taken it on wrong way. i was just not sure that this Book is
    available for all user or not. thats why i requested to share the link only.
    i regret for the inconvenience.
    Thanks for your help
    http://www.google.com/search?q=%22PL%2FSQL+Best+Practices+by+Steven+Feuerstein%22&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-USfficial&client=firefox-a


Posting Permissions

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


Click Here to Expand Forum to Full Width