Oracle if updating column dating explained

CREATE [ OR REPLACE ] TRIGGER trigger_name AFTER INSERT or UPDATE or DELETE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ...

Oracle if updating column

table_name: It specifies the name of the table on which trigger operation is being performed.

CREATE OR REPLACE TRIGGER "SUPPLIERS_T2" AFTER insert or update or delete on "SUPPLIERS" for each row begin when the person performs insert/update/delete operations into the table.

Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE … Once you mark a column as UNUSED, there is no going back to undo the action.

So, marking it INVISIBLE before drop is a good idea.

It is used to re-create the trigger if it already exists.

It facilitates you to change the trigger definition without using a DROP TRIGGER statement.

trigger_name: It specifies the name of the trigger that you want to create.

AFTER INSERT or UPDATE or DELETE: It specifies that the trigger will be fired after the INSERT or UPDATE or DELETE operation is executed.

When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in “SELECT *” and “DESCRIBE”). What happens to the ID columns, when the UNUSED column is dropped?

The COLUMN_ID was reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids – X5 never had a column id assigned as it is INVISIBLE).

2) “conditional update” The instead-of trigger checks to see if the PK columns are being altered, and only includes them if appropriate Nothing crashed – but we totally corrupted the child table.


  1. Rest assured that your privacy is important to us and your details will not be shared with any 3rd party marketing companies.

Comments are closed.