Re: oracle 11g: how to disabled triggers per session

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.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com