1

This is very basic question, but I couldn't find an answer here...

So I have this very useful procedure without any parameters:

CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
BEGIN

  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' DISABLE';
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
END;

And I don't know how to call it...

This way:

begin
    DISABLE_TRIGGERS;
end;

I get a message that the procedure was compiled.

So maybe this way:

begin
    DISABLE_TRIGGERS()
end;

?

Which way is correct?

1
  • I'm using it. So can you tell me how to achieve this using SQLdeveloper?
    – mnowotka
    Oct 26, 2012 at 8:25

1 Answer 1

4

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;
5
  • This will be production code for migration script. What are the consequences?
    – mnowotka
    Oct 25, 2012 at 16:10
  • Your procedure is very helpful, perhaps I should change the question now :)
    – mnowotka
    Oct 25, 2012 at 16:17
  • better yet ask another question about migrations
    – kevinskio
    Oct 25, 2012 at 16:20
  • I have large database where many fields have a type of NUMBER (without precision and scale). I want to use django ORM to create an interface out of this schema. Unfortunately with such a generic types django has no clue how to treat these fields, and that's why I have to change their type do decrease precision and scale. You can't do this on nonempty columns so I'm creating a temporary table and truncate original one. So in the process of copying back and forth I need to temporarily disable all constrains and triggers. Do you have other idea?
    – mnowotka
    Oct 25, 2012 at 16:26
  • Create new schema, import the data into that schema using the old export/import or the datapump which I think will work from self to self. Then, in the new schema, redefine your fields and do the ORM from there. But if you continue to work with the original schema you will have pain later. Best way is to redefine the original schema but this must be tested!
    – kevinskio
    Oct 25, 2012 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.