disable trigger in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: disable trigger in procedure

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    I want to disable the trigger before I do an update and here is the procedure.

    PROCEDURE get_style_properties
    ( sob_key IN number,
    v_sob_sys_fk IN number)
    IS

    v_subj_code varchar2(3);
    v_sob_style number;
    v_errorcode number;
    v_errortext varchar2(200);

    BEGIN

    INSERT INTO DATA_MIGRATION_LOG(TRANS_NUM,SOB_KEY, TRANS_DATE, TRANS_STEP, TRANS_MESSAGE)
    VALUES (TRIG_SEQ.NEXTVAL,sob_key,sysdate, 'GET_STYLE_PROPERTIES','Entered Procedure');

    SELECT SUBJ_CODE
    INTO v_subj_code
    FROM LIS.ALL_AGREEMENTS
    WHERE ARRG_KEY = v_sob_sys_fk;

    IF v_subj_code = 'SVA' THEN
    v_sob_style := 1;
    ELSIF v_subj_code = 'STO' THEN
    v_sob_style := 2;
    ELSIF v_subj_code = 'FAC' THEN
    v_sob_style := 3;
    ELSIF v_subj_code = 'ROW' THEN
    v_sob_style := 4;
    ELSIF v_subj_code = 'AGM' THEN
    v_sob_style := 5;
    ELSIF v_subj_code = 'FEE' THEN
    v_sob_style := 6;
    ELSIF v_subj_code = 'LSE' THEN
    v_sob_style := 7;
    ELSIF v_subj_code = 'CTR' THEN
    v_sob_style := 8;
    ELSIF v_subj_code = 'INA' THEN
    v_sob_style := 9;
    ELSIF v_subj_code = 'MLS' THEN
    v_sob_style := 10;
    ELSE
    v_sob_style := 0;
    END IF;

    execute immediate 'alter trigger row_ins_lis_sob_stg disable';
    execute immediate 'alter trigger stm_ins_lis_sob_stg disable';

    UPDATE LIS_SOB_STG
    SET (SOB_LINE_COLOR,SOB_LINE_STYLE,SOB_LINE_WEIGHT,SOB_EDGE_COLOR,SOB_EDGE_STYLE,SOB_EDGE_WEIGHT,
    SOB_FILL_COLOR,SOB_FILL_BGCOLOR,SOB_FILL_STYLE,SOB_SYMBOL_NAME,SOB_SYMBOL_ROT,SOB_SYMBOL_HEIGHT,SOB_ SYMBOL_WIDTH,
    SOB_SYMBOL_COLOR,SOB_TEXT_VALIGN,SOB_TEXT_HALIGN,SOB_TEXT_HEIGHT,SOB_TEXT_WIDTH,SOB_TEXT_ROT,SOB_TEX T_COLOR,
    SOB_TEXT_BGCOLOR,SOB_TEXT_BGSTYLE,SOB_TEXT_FONTNAME,SOB_TEXT_FONTWEIGHT) =
    (SELECT SOB_LINE_COLOR,SOB_LINE_STYLE,SOB_LINE_WEIGHT,SOB_EDGE_COLOR,SOB_EDGE_STYLE,SOB_EDGE_WEIGHT,
    SOB_FILL_COLOR,SOB_FILL_BGCOLOR,SOB_FILL_STYLE,SOB_SYMBOL_NAME,SOB_SYMBOL_ROT,SOB_SYMBOL_HEIGHT,SOB_ SYMBOL_WIDTH,
    SOB_SYMBOL_COLOR,SOB_TEXT_VALIGN,SOB_TEXT_HALIGN,SOB_TEXT_HEIGHT,SOB_TEXT_WIDTH,SOB_TEXT_ROT,SOB_TEX T_COLOR,
    SOB_TEXT_BGCOLOR,SOB_TEXT_BGSTYLE,SOB_TEXT_FONTNAME,SOB_TEXT_FONTWEIGHT
    FROM LU_STYLES
    WHERE SOB_STYLE = v_sob_style)
    WHERE SOB_KEY = sob_key;


    INSERT INTO DATA_MIGRATION_LOG(TRANS_NUM,SOB_KEY, TRANS_DATE, TRANS_STEP, TRANS_MESSAGE)
    VALUES (TRIG_SEQ.NEXTVAL,sob_key,sysdate, 'GET_STYLE_PROPERTIES','Transfered Style Properties');

    EXCEPTION
    WHEN others THEN
    v_errorcode := SQLCODE;
    v_errortext := SUBSTR(SQLERRM,1,200);
    INSERT INTO DATA_MIGRATION_LOG(TRANS_NUM,SOB_KEY, TRANS_DATE, TRANS_STEP, TRANS_MESSAGE)
    VALUES (TRIG_SEQ.NEXTVAL,sob_key,sysdate, 'GET_STYLE_PROPERTIES','Exception - '||to_char(v_errorcode)||' - '||v_errortext);
    END;


    It's not working , please advise.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Error?
    Version?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    See tips :
    http://www.dbasupport.com/forums/sho...threadid=19583

    about disable triggers in dynamic sql.

  5. #5
    Join Date
    Jan 2002
    Posts
    474
    Thanks for all of your reply.

    I am running 8.1.7.2.5
    and here is the error messages:

    ORA-04092: cannot commit in a trigger.

    any advise ???

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I must simply say that I am amazed that neither the previous thread nor this one has mentioned that fact that unless you have some extreme circumstances going on, you probably do *not* want to do this.

    The worst case, of course, is that we are dealing with a multi-user database, you disable the trigger in this procedure in the middle of the day and *while it is disabled*, another transaction occurs where the trigger *should* have fired but now didn't. Now we have lost data integrity! Triggers are a database designer's way of *guarantee-ing* that certain things happen. Disabling a trigger, for whatever reason, will invalidate that guarantee - not just for the current process, but for the database as a whole.

    Now, if you truly want the trigger *not* to fire for a specific case, then code that into the trigger itself - don't disable the trigger.

    One way is to create a table that holds a SESSION_ID and a trigger name. Before the update statement, insert your session id and the trigger name in the table. In the trigger, check to see if there is an entry in that table telling you not to fire for this statement. If so, exit the trigger code. After the update statement is done - delete the entry - all within a single transaction.

    Such a methodology will allow you to flexibly bypass triggers when necessary without jeapordizing the all-important consistency of your data.

    HTH,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    chris how do you tell a trigger not to fire for a specific session?

  8. #8
    Join Date
    Jan 2002
    Posts
    474
    Chris,

    I do agree but in my case I don't really care about the data since it's the staging table.

    Can you have a DDL inside the package ??? Is that why Oracle complain ???


  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    u can use DDL stmt.
    u hv 2 use execute immediate
    rgds,
    jegan
    Cheers!
    OraKid.

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