Discussions
Categories
- 197.2K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
DDL Triggers invoked on DROP COLUMN in 11.2.0.4.0

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;
Answers
-
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
-
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.
-
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'
-
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?
-
Do you get the issue of you set the column unused?
Yes, same issue: trigger is firing on "SET UNUSED".
-
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.