Hello Everyone,
"Generated always identity" columns are very useful for me. in my customers there are many development team which are inserting to same tables. Some teams are not sending any values for ID columns because they rely on "triggers"! old habits and I am not able to change all of their habits even I am not able to contact some of them. so I had to create a trigger to assign ID column but I never like using triggers (mostly performance issues). I can add "when" clause in trigger and let it run if only id is null but sometimes, a developer which is not experienced, might send random data ind ID column just to accomplish insert and this might violate with my sequence data. if sequence nextval is 10 and app sends 15, after 5 regular rows insert I will get a unique constraint violation error.
so generated always identity columns a perfect for this. no performance issues, value will always be generated from assigned sequence but as a rule, generated always identity columns are not allowed in insert statements and if you try, raise an "ORA-32795: cannot insert into a generated always identity column" error.
I started to use identity columns in my customers dbs but some development teams started to complaint about their own frameworks are not support to remove a column (especially primary key) from their inserts. I advise them to remove column completely from their definitions but this time they said that update statements are also raising error because lack of id column.
I am aware that they must be using necessary sequences in their insert statements but I am not able to interfere their job. I don't even know some teams which are 3rd party sources.
So it would be nice if there is an option in generated always identity column allow using in insert statements but simply just ignoring that data (whatever they send) and generate new ID. that way I will be sure my id's are generated from a sequence, no violation and it is not important what developers send from application.
thanks.