Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
oracle 11g: how to disabled triggers per session
1,742 views
Skip to first unread message
rgr...@gmail.com
Feb 4, 2014, 10:03:02 AM2/4/14
to
Hello,
Do You know any trick to disabled all triggers but only per session ?
I can't use dbms_xstream_gg.set_foo_trigger_session_contxt becouse I have Standard Edition.
I found the /*+ NO_TRIGGER */ hint method, but unfortunatelly it doesn't work.
I won't use method: add any extra line to the begining of all triggers, for example to check same context or samething like this... because it is a large database.
Regards
Robert
Do You know any trick to disabled all triggers but only per session ?
I can't use dbms_xstream_gg.set_foo_trigger_session_contxt becouse I have Standard Edition.
I found the /*+ NO_TRIGGER */ hint method, but unfortunatelly it doesn't work.
I won't use method: add any extra line to the begining of all triggers, for example to check same context or samething like this... because it is a large database.
Regards
Robert
Mladen Gogala
Feb 4, 2014, 11:10:43 AM2/4/14
to
first place? Triggers are used to enforce business rules. What rules
would you want to bypass? Is it legal?
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
joel garry
Feb 4, 2014, 11:33:24 AM2/4/14
to
jg
--
@home.com is bogus.
http://www.forbes.com/sites/oracle/2014/02/03/oracles-larry-ellison-how-to-manage-your-most-precious-assets/
rgr...@gmail.com
Feb 4, 2014, 4:48:39 PM2/4/14
to
I would like to disable because in specyfic sytuations before delete a part of related data from database I generate script which can back this operation after commit (for example in case of user mistake) and in this case the triggers can disturb same data...
'Triggers are used to enforce business rules' - not only... are you writing about audit ?
'Is it legal?' - why not ? what is inlegal in disable trigger ????????
Regards
joel garry
Feb 4, 2014, 6:04:27 PM2/4/14
to
It becomes illegal when the triggers are there to enforce business rules based on laws. For example, if your boss tells you to disable a trigger that audits US securities transactions, and you do it, and he steals money, you can go to jail.
If your requirements are too hard, hire somebody else. If you need Enterprise Edition, get Enterprise Edition. If you want to use undocumented features, You Are On Your Own.
If you are legitimately doing something that requires users not be on, don't let users be on. If you are trying to fix user error, consider logical backup, covering all tables impacted.
http://www.informationweek.com/strategic-cio/digital-business/oracles-hurd-a-once-in-a-career-opportunity/d/d-id/1113622
Peter Schneider
Feb 4, 2014, 6:38:36 PM2/4/14
to
Am 04.02.2014 17:33, schrieb joel garry:
> On Tuesday, February 4, 2014 7:03:02 AM UTC-8, rgr...@gmail.com wrote:
>> Hello,
>>
>>
>>
>> Do You know any trick to disabled all triggers but only per session ?
>>
>> I can't use dbms_xstream_gg.set_foo_trigger_session_contxt becouse I have Standard Edition.
>>
>> I found the /*+ NO_TRIGGER */ hint method, but unfortunatelly it doesn't work.
>>
>> I won't use method: add any extra line to the begining of all triggers, for example to check same context or samething like this... because it is a large database.
>>
>>
>>
>> Regards
>>
>> Robert
>
> What does the size of the database have to do with it? What does the trigger do that you want to disable it?
I guess he meant he didn't want to change hundreds or even thousands of
> On Tuesday, February 4, 2014 7:03:02 AM UTC-8, rgr...@gmail.com wrote:
>> Hello,
>>
>>
>>
>> Do You know any trick to disabled all triggers but only per session ?
>>
>> I can't use dbms_xstream_gg.set_foo_trigger_session_contxt becouse I have Standard Edition.
>>
>> I found the /*+ NO_TRIGGER */ hint method, but unfortunatelly it doesn't work.
>>
>> I won't use method: add any extra line to the begining of all triggers, for example to check same context or samething like this... because it is a large database.
>>
>>
>>
>> Regards
>>
>> Robert
>
> What does the size of the database have to do with it? What does the trigger do that you want to disable it?
trigger manually?
Regards
Peter
--
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you. -- David McCullough Jr.
Mladen Gogala
Feb 4, 2014, 9:18:51 PM2/4/14
to
On Tue, 04 Feb 2014 13:48:39 -0800, rgrzes1 wrote:
> why magic ?
Sufficiently advanced database administration looks like magic to jr. DBA
personnel.
> why there is no ? there is ->
> dbms_xstream_gg.set_foo_trigger_session_contxt but not in Standard
> Edition
That sounds like something that a nice check made to Oracle Corp. could
easily solve. The "gg" comes from Golden Gate. The legend has it that if
you bring your database server to the Golden Gate Bridge, the procedure
in question might work even on the standard edition. Just don't confuse
it with the George Washington Bridge between New York City and Fort Lee,
NJ, as that one may be problematic. Fortunately, those two bridges are a
few miles apart, so that it's hard to make such a mistake.
> I would like to disable because in specyfic sytuations before
> delete a part of related data from database I generate script which can
> back this operation after commit (for example in case of user mistake)
> and in this case the triggers can disturb same data...
You can disable triggers on the tables, no need to do it for the session.
From my ample experience, I can tell you that you should be careful when
deleting data and not make it too easy or trivial. Here is how to do it:
SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:00.32
SQL> alter table emp1 disable all triggers;
Table altered.
Elapsed: 00:00:00.12
SQL> alter table emp1 enable all triggers;
Table altered.
Deleting large amounts of data from the database is a sport which
requires maintenance and the absence of the normal users from the system.
It's usually done at the DBA time of the night, between 2AM and 5AM.
And triggers do not "disturb" data, they keep the data logically
consistent and in accordance with the business rules.
>
> 'Triggers are used to enforce business rules' - not only... are you
> writing about audit ?
> 'Is it legal?' - why not ? what is inlegal in disable trigger ????????
No, I am writing about things like HIPAA and SOX, two laws, by no means
the only ones, that regulate the information handling in some rather
ubiquitous cases. And yes, it can be illegal to turn off auditing on some
databases, especially if financial information or the patient info is
involved.
> why magic ?
Sufficiently advanced database administration looks like magic to jr. DBA
personnel.
> why there is no ? there is ->
> dbms_xstream_gg.set_foo_trigger_session_contxt but not in Standard
> Edition
easily solve. The "gg" comes from Golden Gate. The legend has it that if
you bring your database server to the Golden Gate Bridge, the procedure
in question might work even on the standard edition. Just don't confuse
it with the George Washington Bridge between New York City and Fort Lee,
NJ, as that one may be problematic. Fortunately, those two bridges are a
few miles apart, so that it's hard to make such a mistake.
> I would like to disable because in specyfic sytuations before
> delete a part of related data from database I generate script which can
> back this operation after commit (for example in case of user mistake)
> and in this case the triggers can disturb same data...
From my ample experience, I can tell you that you should be careful when
deleting data and not make it too easy or trivial. Here is how to do it:
SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:00.32
SQL> alter table emp1 disable all triggers;
Table altered.
Elapsed: 00:00:00.12
SQL> alter table emp1 enable all triggers;
Table altered.
Deleting large amounts of data from the database is a sport which
requires maintenance and the absence of the normal users from the system.
It's usually done at the DBA time of the night, between 2AM and 5AM.
And triggers do not "disturb" data, they keep the data logically
consistent and in accordance with the business rules.
>
> 'Triggers are used to enforce business rules' - not only... are you
> writing about audit ?
> 'Is it legal?' - why not ? what is inlegal in disable trigger ????????
the only ones, that regulate the information handling in some rather
ubiquitous cases. And yes, it can be illegal to turn off auditing on some
databases, especially if financial information or the patient info is
involved.
rgr...@gmail.com
Feb 5, 2014, 5:46:12 AM2/5/14
to
In my case disable triggers is of course legal and conscious
But of course I understand if in many cases it can be inlegal or can take same related problems -> it depends on special case :)
I realized it by automatic adding an extra first line on the begining of any triggers that checks same session parameter.
Thanks for help
Regards
Robert
joel garry
Feb 5, 2014, 11:43:39 AM2/5/14
to
On Tuesday, February 4, 2014 3:38:36 PM UTC-8, Peter Schneider wrote:
> Am 04.02.2014 17:33, schrieb joel garry:
>
> > What does the size of the database have to do with it? What does the trigger do that you want to disable it?
>
> I guess he meant he didn't want to change hundreds or even thousands of
> trigger manually?
Since he's already done it by adding one line at the beginning of the trigger, I guess not.
> Am 04.02.2014 17:33, schrieb joel garry:
>
> > What does the size of the database have to do with it? What does the trigger do that you want to disable it?
>
> I guess he meant he didn't want to change hundreds or even thousands of
> trigger manually?
We always need to scope the problem before deciding how to solve it, or if it is even a problem.
http://www.webwire.com/ViewPressRel.asp?aId=185127#.UvJp2vvprFw
Peter Schneider
Feb 5, 2014, 3:58:27 PM2/5/14
to
Am 05.02.2014 17:43, schrieb joel garry:
> On Tuesday, February 4, 2014 3:38:36 PM UTC-8, Peter Schneider wrote:
>> Am 04.02.2014 17:33, schrieb joel garry:
>>
>>> What does the size of the database have to do with it? What does the trigger do that you want to disable it?
>>
>> I guess he meant he didn't want to change hundreds or even thousands of
>> trigger manually?
>
> Since he's already done it by adding one line at the beginning of the trigger, I guess not.
>
> We always need to scope the problem before deciding how to solve it, or if it is even a problem.
Oh, I absolutely like to solve interesting things that are not a problem at
> On Tuesday, February 4, 2014 3:38:36 PM UTC-8, Peter Schneider wrote:
>> Am 04.02.2014 17:33, schrieb joel garry:
>>
>>> What does the size of the database have to do with it? What does the trigger do that you want to disable it?
>>
>> I guess he meant he didn't want to change hundreds or even thousands of
>> trigger manually?
>
> Since he's already done it by adding one line at the beginning of the trigger, I guess not.
>
> We always need to scope the problem before deciding how to solve it, or if it is even a problem.
all, just because they are interesting :-)
Mladen Gogala
Feb 5, 2014, 4:30:39 PM2/5/14
to
On Wed, 05 Feb 2014 21:58:27 +0100, Peter Schneider wrote:
> Oh, I absolutely like to solve interesting things that are not a problem
> at all, just because they are interesting :-)
I like that too, but nobody wants to pay me for that. I do have to think
> Oh, I absolutely like to solve interesting things that are not a problem
> at all, just because they are interesting :-)
in that boring way: "what's good for the company". It's sometimes
something different from what I would like to do. Unfortunately, I'm not
a man of independent means and I need my salary to buy my daily bread,
butter and ammo.
0 new messages