Q
Problem solve Get help with specific problems with your technologies, process and projects.

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> 
Making this procedure autonomous is not absolutely necessary as the default behavior of how procedures run will give you the results you want. But by making it autonomous, you can execute the ALTER ddl commands you desire and have it execute as a separate transaction which would allow your main transaction to be able to commit/rollback independently.

For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close