http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660I have been able to get Heiko's proposed solution (the one with the "returning" statement in the insert) to work, but unfortunately I have had to make some modifications to NHibernate's source code. I have explained it all here: http://forum.hibernate.org/viewtopic.php?t=980364 Any advice would be highly appreciated! http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment13http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment13Sun, 14 Oct 2007 21:18:34 GMTThat is not something that I would worry about, frankly. If someone can already execute SQL on my DB, they already in the position to make much mischief, no point in trying to add layers of protections that doesn't really protect you. http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment12http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment12Sat, 13 Oct 2007 11:47:33 GMTThanks for th reply. But don't you make your database vulnerable for bad insert statements then? Imagine a developer that uses the wrong sequence in his insert statement, or just provides his own Id. http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment11http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment11Sat, 13 Oct 2007 11:39:09 GMTTolomaüs, It is considered bad because there are existing ways of dealing with that, which take less time and are far more explicit. In general, it is good to avoid triggers in the first place http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment10http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment10Sat, 13 Oct 2007 06:30:01 GMTCan someone explain me why this is bad database design? I have been googling for a good explanation but I can't seem to find it ... I have taken over an Oracle project some time ago and the first thing I did was removing the PK generation from the insert trigger, because NHibernate doesn't support it out-of-the-box. Today our most experienced Oracle DBA told us that he promotes this design. Thanks Tolomaüs http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment9http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment9Fri, 12 Oct 2007 23:18:53 GMTWell... Wouldnt the "correct" Querry get the NEXTVAL before executing the trigger, and supplying the value for the ID field and thus avoiding the trigger? (At least "our" triggers behave that way) You cannot be sure that the currentVal is the Val that was used to insert YOUR row, since a seq. isnt "transactionally safe" P.s. I know i nitpick... ;) Another way you can use is to utilize the insert into foobar... returning ID into whatever But in sumary I agree its best to shoot someone for doing this ;) http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment8http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment8Wed, 10 Oct 2007 10:42:57 GMTThe correct query is SELECT sequence.CURRVAL from DUAL; ;) http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment7http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment7Wed, 10 Oct 2007 06:25:29 GMTKen, It's called - Legacy Systems. And you can't go around changing 2,000 tables (+Triggers + PK) because you said "well, I kinda don't like it with no actual reason". Yes, Triggers aren't fun, but this is the DEFAULT oracle way of creating Identity PK. Blame Oracle 7 for that - It's just the way it is now days. Seriously, Saying "this is wrong" on an oracle DB design is like saying the sky is blue. It's redundant. But DBs still exist and Applications need to able to work with them. http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment6http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment6Wed, 10 Oct 2007 00:52:29 GMTKen, I don't like it either, and NH has native support for sequences, this is for the cases where you need it, in hope you don't http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment5http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment5Tue, 09 Oct 2007 18:44:19 GMTAs I said, this is notepad code, I don't even have an oracle here to test it. http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment4http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment4Tue, 09 Oct 2007 18:43:22 GMTYeah, trigger-generated values are kind of obsolete, but trigger-generated PK values are just WRONG. Only SQL Server guys don't understand that sequences are just better than identity columns... http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment3http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment3Tue, 09 Oct 2007 17:38:11 GMTI have a better solution. instead of executing the trigger, you should be executing the person who wrote it, and then use a Sequence instead http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment2http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment2Tue, 09 Oct 2007 16:37:16 GMTit won't work unless you change it to return "select mySequnece_"+tableName+".currentValue() from dual; " http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment1http://ayende.com/2852/how-to-get-nhibernate-to-work-with-pk-values-generated-by-a-trigger#comment1Tue, 09 Oct 2007 16:31:56 GMT