If it does not take any parameters in or out then
BEGIN
DISABLE_TRIGGERS;
END;
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
something like
CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
IS
v_statement VARCHAR2(500);
CURSOR trigger_cur
IS
SELECT trigger_name
FROM user_triggers;
v_action VARCHAR2(200);
BEGIN
ASSERT (action_in IN (0,1)); --where assert is part of your own error package
IF action_in = 0
THEN
v_action := 'DISABLE';
ELSIF action_in = 1
THEN
v_action := 'ENABLE';
END IF;
FOR i in trigger_cur
LOOP
v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
EXECUTE IMMEDIATE v_statement;
END LOOP;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP);
--ELSE
--log result to a table
--END IF;
END CHANGE_TRIGGERS;