Forum Stats

  • 3,824,835 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

Disable triggers just for current session.

Mustafa_KALAYCI
Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
edited Sep 14, 2020 10:06AM in Database Ideas - Ideas

during a maintenance operation on the table, I would like to disable table triggers for just current session! for example I would like to archive some rows using "row archival" but when I updated rows for ora_archive_state='1', a before update trigger causes to change update_date and update_user columns but actually this is not an application operation so it shouldn't be firing triggers. Also this could be used to prevent some logging operation via triggers or (unfortunately) a trigger calling a web service etc. of course we can add a WHEN clause to trigger and filter the operation with action/module/client_identiferi/user etc but if I need to do this operation for many tables, every trigger on those tables must be changed and developers must be developed their code based on that. forcing a developer or at least leaving the option to do this might be terrible idea. so as a database admin or developer admin, I would be glad if I can disable triggers on a table just for that session.

it could be something like:

Alter table A_table disable all triggers for current session;

thanks.

edit: when I googled this, I found a package :sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); bu undocumented and can be used for only goldengate. So this has already been implemented.

Sven W.KayKWilliam RobertsonulohmannUser_RR3PWJason_(A_Non)Niels HeckerUser_G3UV3
8 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    This is a common requirement. For some (batch?) operations we want to not execute the trigger.

    Typically this is solved by using some package variable that can be set/unset at session level and is checked inside the trigger.

    It could be useful (and more performant) to have a different session based solution.

    Mustafa_KALAYCI
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    This is a common requirement. For some (batch?) operations we want to not execute the trigger.

    Typically this is solved by using some package variable that can be set/unset at session level and is checked inside the trigger.

    It could be useful (and more performant) to have a different session based solution.

    exactly, setting a package variable (or using a context variable) is the only solution now but if there are so many triggers and new triggers can be added, developer must add those controls otherwise it won't work. thanks for support Sven.

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 63 Bronze Badge

    Maybe it would be possible to create an "edition" of "empty" triggers and run such sessions in that edition ?

    Mustafa_KALAYCI
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Maybe it would be possible to create an "edition" of "empty" triggers and run such sessions in that edition ?

    Yes that is a good idea and it would work.
    If editions are enabled one could create a new child edition and drop the triggers there.

    Then run the logic in this child edition.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown

    Maybe it would be possible to create an "edition" of "empty" triggers and run such sessions in that edition ?

    Hi Rainer,

    this is a good idea, I will test it but still it requires more effort then a "alter session disable triggers" kind of command.