Disabling and enabling triggers

I need the ability to disable and enable triggers, for example aaa schema, while being logged into aaahist schema. The only way we can come up with is to give aaahist the system privilege "ALTER ANY TRIGGER", which we are not going to do because aaahist would be able to alter any trigger in any schema.

You can do this with a stored procedure very easily. Also, by using the autonomous_transaction pragma when you create the procedure, you allow the procedure to run without effecting the transaction running in the session that calls the procedure. Note that by default all procedures run with the permissions of the owner of the procedure (this is called definer rights). So, even though user B runs the procedure, what the procedure does will effect the objects of the owner of the procedure (user A). Take a look at the following example.

SQL> show user
USER is "KAREN"
SQL> create or replace procedure chg_trig_status (p_status user_triggers.status%TYPE)
  2  as
  3     pragma autonomous_transaction;
  4     cursor trig_cur is
  5     select trigger_name
  6       from user_triggers ;
  7     trig_rec  trig_cur%ROWTYPE ;
  8     v_stmt  varchar2(200) ;
  9  begin
 10     open trig_cur ;
 11     loop
 12         fetch trig_cur into trig_rec ;
 13         exit when trig_cur%NOTFOUND ;
 14         v_stmt := 'ALTER TRIGGER ' || trig_rec.trigger_name || ' ' || p_status ;
 15         execute immediate v_stmt ;
 16     end loop ;
 17     close trig_cur ;
 18     commit;
 19  end;
 20  /

Procedure created.

SQL> select trigger_name, status from user_triggers ;

TRIGGER_NAME                   STATUS
------------------------------ --------
RLIMITMAJORS                   DISABLED
SLIMITMAJORS                   DISABLED

SQL> create public synonym chg_trig_status for chg_trig_status ;

Synonym created.

SQL> grant execute on chg_trig_status to nova ;

Grant succeeded.

SQL> conn nova
Enter password: *******
Connected.
SQL> show user
USER is "NOVA"

SQL> execute chg_trig_status ('ENABLE') 

PL/SQL procedure successfully completed.

SQL> conn karen
Enter password: *******
Connected.
SQL> select trigger_name, status from user_triggers ;

TRIGGER_NAME                   STATUS
------------------------------ --------
RLIMITMAJORS                   ENABLED
SLIMITMAJORS                   ENABLED

SQL> 

For More Information

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.