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
- 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.
