disable trigger in procedure

[ashley75]

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.

[marist89]

Error?
Version?

[marist89]

Also, you may want to follow http://www.dbasupport.com/forums/sho...threadid=19583

[Shestakov]

See tips :
http://www.dbasupport.com/forums/sho...threadid=19583

about disable triggers in dynamic sql.

[ashley75]

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 ???

[chrisrlong]

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

[pando]

chris how do you tell a trigger not to fire for a specific session?

[ashley75]

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 ???

[balajiyes]

u can use DDL stmt.
u hv 2 use execute immediate
rgds,
jegan