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.