Forum Stats

  • 3,838,102 Users
  • 2,262,329 Discussions
  • 7,900,502 Comments

Discussions

Categories

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:
· Share on TwitterShare on Facebook

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

    · Share on TwitterShare on Facebook
  • Unknown
    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.

    · Share on TwitterShare on Facebook
  • 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'
    · Share on TwitterShare on Facebook
  • 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?

    · Share on TwitterShare on Facebook
  • 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".

    · Share on TwitterShare on Facebook
  • Unknown
    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.

    · Share on TwitterShare on Facebook
This discussion has been closed.