Forum Stats

  • 3,839,830 Users
  • 2,262,539 Discussions
  • 7,901,065 Comments

Discussions

Disable or drop trigger, is there a difference?

651980
651980 Member Posts: 8
edited Sep 2, 2009 6:01AM in General Database Discussions
Firstly let me explain the scenario, a developer created a trigger on a TABLE owned by another schema to insert into his own schema. Anyway it did not work and gave him various errors through TOAD that I do not have copies of. He disabled the trigger but the errors continued. It was only when the trigger was dropped that the error ceased. My question is Is there something different between disabling a trigger and dropping it apart from the obvious? I also have to explain that this is on a very old database version 7.3.4.4.0 on solaris. I do know why disabling it would not mean it was ignored therefore effectively acting as if it did not exist at all much like if it was dropped from the database. Any suggestions?

The trigger was eventually recreated and a public synonym creation on the table that was to be inserted into solved the errors. But why did disabling the trigger still mean errors where thrown out.

Edited by: user648977 on 02-Sep-2009 00:32
Tagged:

Answers

  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    By disabling the trigger, you can enable it whenever you want. By deleting it, you're not able to use it anymore

    I don't think that the user was getting the error due to the trigger while it was disabled
  • 670456
    670456 Member Posts: 38
    Hi,

    disable of trigger means you are just disable the particular trigger not to function , It will remain in the database. You can see that trigger by "dba_objects" data dictionary.
    But droping means you are removing the trigger from database itself . You can't see that trigger name in "dba_objects" data dictionary.

    Regards,
    Shekhar
  • 651980
    651980 Member Posts: 8
    thanks to you both for your replys, I am well aware of database views and the physical difference between by disbale and drop, that is not my query, my query is that if you disable an object like a trigger is it possible that some other object may still refer to it or call on it even if that call is to make sure it is diabled.I have also thought that the issue may not be the trigger, but dropping it did mean the errors where no longer apparent.
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    If you have a table with trigger, and you disable that trigger, table will function normally and WILL NOT use disabled trigger. You can test it by your own.
  • 651980
    651980 Member Posts: 8
    what if the trigger was invalid before being disabled and was then disabled, is it still properly being disabled?
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    user648977 wrote:
    what if the trigger was invalid before being disabled and was then disabled, is it still properly being disabled?
    If trigger is INVALID, you can't use TABLE which uses that Trigger. You'll get an error:
    ORA-04098: trigger 'USER.TRG_TEST' is invalid and failed re-validation
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    user648977 wrote:
    what if the trigger was invalid before being disabled and was then disabled, is it still properly being disabled?
    Let's demonstrate it with simple example:
    SQL> create table test (id number);
    
    Table created.
    
    SQL> create table test2 (ddate date);
    
    Table created.
    
    SQL> create or replace trigger trg_test before insert on test
      2  begin
      3  insert into test2 values(sysdate);
      4  end;
      5  /
    
    Trigger created.
    
    SQL> insert into test values(1);
    
    1 row created.
    
    SQL> select * from test2;
    
    DDATE
    ---------
    02-SEP-09
    
    SQL> alter trigger trg_test disable;
    
    Trigger altered.
    
    SQL> insert into test values(2);
    
    1 row created.
    
    SQL> select * from test2;
    
    DDATE
    ---------
    02-SEP-09
    
    SQL> alter trigger trg_test enable;
    
    Trigger altered.
    
    SQL> create or replace trigger trg_test before insert on test
      2  begin
      3  insert intoooooooooooooooo test values(sysdate);
      4  end;
      5  /
    
    Warning: Trigger created with compilation errors.
    
    SQL> insert into test values(3);
    insert into test values(3)
                *
    ERROR at line 1:
    ORA-04098: trigger 'USER.TRG_TEST' is invalid and failed re-validation
    
    
    SQL> select * from test;
    
            ID
    ----------
             1
             2
    
    SQL> select * from test2;
    
    DDATE
    ---------
    02-SEP-09
    
    SQL>
  • 651980
    651980 Member Posts: 8
    It should not matter if the trigger is invalid or not, because the disable function should by pass the trigger. What do you mean the table is unusable, do you mean that the trigger is unusable, the table would still be usable for queries, updates etc, just no through the trigger.
This discussion has been closed.