If it does not take any parameters in or out then
Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;
I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences. This code as originally posted should be more comprehensive if used in production, particularly for a migration. Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.
- Does the migration depend on any business logic in the triggers?
- Will users be able to access the database during the migration? Hope not, any actions they take will not use the trigger logic.
- does the success or failure of the migration depend on triggers being disabled?
You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.
A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers
CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
ASSERT (action_in IN (0,1)); --where assert is part of your own error package
IF action_in = 0
v_action := 'DISABLE';
ELSIF action_in = 1
v_action := 'ENABLE';
FOR i in trigger_cur
v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
EXECUTE IMMEDIATE v_statement;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP);
--log result to a table