Forum Stats

  • 3,838,105 Users
  • 2,262,332 Discussions
  • 7,900,506 Comments

Discussions

DDL Triggers invoked on DROP COLUMN in 11.2.0.4.0

user10881119
user10881119 Member Posts: 3
edited Sep 25, 2015 5:17PM in General Database Discussions

Hi,


If you run the following code snippet below the trigger on the table is invoked when a clumn is dropped which had been added after the table creation and has a default value and not null defined. This is behaviour I could only reproduce in Oracle 11.2.0.4.0 but not in 11.2.0.3.0. COuld somebody please help me with this? Is this expected behaviour? How can I drop the column without running the trigger?


CREATE TABLE T (

    DESCRIPTION VARCHAR2(50)

);


INSERT INTO T (DESCRIPTION) VALUES ('asd');


COMMIT;


CREATE OR REPLACE TRIGGER BEFORE_T

  BEFORE INSERT OR DELETE OR UPDATE ON T

    REFERENCING OLD AS OLD NEW AS NEW

    FOR EACH ROW

BEGIN

    RAISE_APPLICATION_ERROR(-20001, 'This trigger should not be invoked!');

END BF_TRANSACTIONS;

/

ALTER TABLE T ADD AMOUNT NUMBER DEFAULT 0 NOT NULL;


ALTER TABLE T DROP COLUMN AMOUNT;

Tagged:

Answers

  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited Sep 24, 2015 7:57PM

    Hi,

    In 11.2.0.3, results:

    Table T creado.
    
    1 fila insertadas.
    
    Confirmación terminada.
    Trigger BEFORE_T compilado
    
    Table T alterado.
    
    Table T alterado.
    
    

    No errors, but you mentioned that.

    What about using compound trigger:

    CREATE OR REPLACE TRIGGER BEFORE_T
      for INSERT OR DELETE OR UPDATE ON T
      COMPOUND TRIGGER
      BEFORE EACH ROW
      IS
      BEGIN
        RAISE_APPLICATION_ERROR(-20001, 'This trigger should not be invoked!');
    END BEFORE EACH ROW;
    END BEFORE_T;
    /
    

    Regards,

    Juan M

  • Unknown
    edited Sep 24, 2015 8:35PM
    If you run the following code snippet below the trigger on the table is invoked when a clumn is dropped which had been added after the table creation and has a default value and not null defined. This is behaviour I could only reproduce in Oracle 11.2.0.4.0 but not in 11.2.0.3.0. COuld somebody please help me with this? Is this expected behaviour? How can I drop the column without running the trigger?

    You have NOT shown that the trigger has been invoked Why not? Post an EXACT copy of your sql*plus session showing the actual execution of what you posted and the exception.

    If the trigger is firing that would be a bug.

    Search MOS to see if a bug has already been filed.

    If not submit an SR to Oracle.

    The ONLY workaround I'm aware of is to disable the trigger before the operation and then reenable it.

  • user10881119
    user10881119 Member Posts: 3
    edited Sep 25, 2015 10:49AM

    Hi,

    Please find the output of sql*plus below.

    SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 25 16:28:52 2015
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
    SQL>
    SQL> DROP TABLE T PURGE;
    Table dropped.
    SQL>
    SQL> CREATE TABLE T (
      2      DESCRIPTION VARCHAR2(50)
      3  );
    Table created.
    SQL>
    SQL> INSERT INTO T (DESCRIPTION) VALUES ('asd');
    1 row created.
    SQL>
    SQL> COMMIT;
    Commit complete.
    SQL>
    SQL> CREATE OR REPLACE TRIGGER BEFORE_T_U
      2      BEFORE UPDATE ON T
      3      REFERENCING OLD AS OLD NEW AS NEW
      4      FOR EACH ROW
      5  BEGIN
      6      RAISE_APPLICATION_ERROR(-20001, 'This before update trigger should not be invoked!');
      7  END BEFORE_T_U;
      8  /
    Trigger created.
    SQL>
    SQL> ALTER TABLE T ADD AMOUNT NUMBER DEFAULT 0 NOT NULL;
    Table altered.
    SQL>
    SQL> ALTER TABLE T DROP COLUMN AMOUNT;
    ALTER TABLE T DROP COLUMN AMOUNT
    *
    ERROR at line 1:
    ORA-20001: This before update trigger should not be invoked!
    ORA-06512: at "ABPXE.BEFORE_T_U", line 2
    ORA-04088: error during execution of trigger 'ABPXE.BEFORE_T_U'
    
  • JohnWatson2
    JohnWatson2 Member Posts: 4,470 Silver Crown
    edited Sep 25, 2015 10:58AM

    THis is 12.1.0.2,

    orclz>

    orclz> CREATE TABLE T (

      2      DESCRIPTION VARCHAR2(50)

      3  );

    Table created.

    orclz> INSERT INTO T (DESCRIPTION) VALUES ('asd');

    1 row created.

    orclz>

    orclz> COMMIT;

    Commit complete.

    orclz>

    orclz> CREATE OR REPLACE TRIGGER BEFORE_T

      2    BEFORE INSERT OR DELETE OR UPDATE ON T

      3      REFERENCING OLD AS OLD NEW AS NEW

      4      FOR EACH ROW

      5  BEGIN

      6      RAISE_APPLICATION_ERROR(-20001, 'This trigger should not be invoked!');

      7  END BF_TRANSACTIONS;

      8  /

    Trigger created.

    orclz> ALTER TABLE T ADD AMOUNT NUMBER DEFAULT 0 NOT NULL;

    Table altered.

    orclz> ALTER TABLE T DROP COLUMN AMOUNT;

    Table altered.

    orclz> select * from v$version where rownum=1;

    BANNER                                                                              CON_ID

    -------------------------------------------------------------------------------- ----------

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

    orclz>

    Do you get the issue of you set the column unused?

  • user10881119
    user10881119 Member Posts: 3
    edited Sep 25, 2015 4:52PM
    Do you get the issue of you set the column unused?
    

    Yes, same issue: trigger is firing on "SET UNUSED".

  • Unknown
    edited Sep 25, 2015 5:17PM
    Yes, same issue: trigger is firing on "SET UNUSED".
    

    No one on the forums can help you with Oracle bugs.

    You need to contact Oracle as I first said.

This discussion has been closed.