2 Replies Latest reply: Jul 3, 2007 1:05 AM by Madrid RSS

    Enable and Disable Triggers and Constraints in Oracle 9i

    584206
      I need to restore a table from a backup and before i restore i need to disable and enable the constraints and Triggers. How do i do this and also how can i disable and enable Triggers and Constraints.

      Thanks
        • 1. Re: Enable and Disable Triggers and Constraints in Oracle 9i
          547086
          Hi, you will must find the trigger and constraint and disable manually. Find the triggers on DBA_TRIGGERS and DBA_CONSTRAINTS, and disable to them manually. Also you can use SQL Developer for make that with GUI.

          Luck.

          Have a good day.

          Regards-
          • 2. Re: Enable and Disable Triggers and Constraints in Oracle 9i
            Madrid
            Is this target table already loaded in your database?

            I figure out, even though you don't explicitly mention it, it comes from a regular export file, isn' it?

            You should first verify it there is an already associated trigger with it, if this is the case, you should temporarily disable by issuing this command:

            ALTER TRIGGER <trigger_name> DISABLE; -- to disable an specific trigger
            or
            ALTER TABLE <table_name> DISABLE ALL TRIGGERS; -- to disable all associated trg.

            In case the table already exists.

            On the constraints issue, you should disable them in case table already exists.
            example.
            ALTER TABLE <table_name> DISABLE PRIMARY KEY; -- or any other constraint.

            If table doesn't exist and you don't want to import constraints along the table specify the CONSTRAINTS=N at import time.


            ~ Madrid.