+972 52-548-6969
How to get NHibernate to work with PK values generated by a trigger?
time to read 1 min | 99 words
Well, the correct answer is to use a <generator type="select">, but that hasn't been ported to NHinbernate yet, so a quick & dirty solution may be this piece of code, that I just wrote in Notepad and I have no idea if it really works :-)
public class MyOracleDialect : OracleDialect { public override bool SupportsIdentityColumns { get { return true; } } public override string GetIdentitySelectString(string identityColumn, string tableName) { return "select mySequnece_"+tableName+".currnetValue() from dual; "; } }
Comments
it won't work unless you change it to
return "select mySequnece_"+tableName+".currentValue() from dual; "
I have a better solution.
instead of executing the trigger, you should be executing the person who wrote it, and then use a Sequence instead
Yeah, 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...
As I said, this is notepad code, I don't even have an oracle here to test it.
Ken,
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
Ken,
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.
The correct query is
SELECT sequence.CURRVAL from DUAL;
;)
Well...
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 ;)
Can 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
Tolomaü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
Thanks 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.
That 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.
I 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!
Comment preview