Derive your dialect from the sql server one and override the select identity property IIRC
On Fri, Oct 3, 2008 at 11:59 PM, MAMMON <[EMAIL PROTECTED]> wrote: > > While it seemed that using the INSTEAD OF triggers on my readonly view > was going to be an easy fix, I now have problems with > SCOPE_IDENTITY(); > > When a new record is created by calling INSERT on the view, the > "scope" of SCOPE_IDENTITY() becomes the SQL that executes in the > trigger, not the SQL statement that calls INSERT on the view, so when > NHibernate tries to call SCOPE_IDENTITY() to get the ID of the newly > inserted record, it returns NULL. > > Anyone know how to properly map a view using INSTEAD OF triggers? If > there is a way to cause NHibernate to use @@IDENTITY instead of > SCOPE_IDENTITY(), that would fix it. I would prefer to use @@IDENTITY > *just* for the classes that are mapped to views using INSTEAD OF > INSERT triggers, and leave everything else using SCOPE_IDENTITY(). > Any help is appreciated! > > On Oct 3, 10:31 am, MAMMON <[EMAIL PROTECTED]> wrote: > > I knew about AFTER triggers (also called FOR triggers), but I didn't > > know about INSTEAD OF triggers, or that they could be created on > > views. You sir, have just solved my entire problem. Thank you!!!! > > > > For anyone who has a similar problem, or has been following this > > thread, an INSTEAD OF trigger can be specified for UPDATE, INSERT, or > > DELETE, and the SQL defined in the trigger will fire "instead of" the > > triggering action. So this: > > > > UPDATE vw_users SET FName='Sam' WHERE UserID = 123 > > > > Would cause the trigger to fire instead of making any attempt at > > updating vw_users (which would fail anyway, since it is a read only > > view). The SQL for the trigger looks like this: > > > > ALTER TRIGGER dbo.tr_vw_users_Update > > ON dbo.vw_users > > INSTEAD OF UPDATE > > AS > > BEGIN > > -- SET NOCOUNT ON added to prevent extra result sets from > > -- interfering with SELECT statements. > > SET NOCOUNT ON; > > > > -- Update dbo.UserInfo, not dbo.vw_users > > UPDATE ui > > SET > > ui.FName = ins.FName > > FROM > > inserted ins > > INNER JOIN dbo.UserInfo ui > > ON ins.UserID = ui.UserID > > END > > > > Now my "readonly" view suddenly supports UPDATE statements! I can do > > the same for DELETE and INSERT, and then in NHibernate, I can just map > > to the view all day long. Thank you Stefan!!! > > ... > > > > So we want the FName from our userInfo table if there is one, and if > > not, we'll fall back on the hr table. INSERT, UPDATE, and DELETE > > statements can't be done on this view because there's no way for SQL > > Server to know which table to act on for those coalesced fields. > > > > On Oct 3, 12:56 am, Stefan Nobis <[EMAIL PROTECTED]> wrote: > > > > > MAMMON <[EMAIL PROTECTED]> writes: > > > > The problem with views like this is that they are read only. > > > > > Why? Do you know about triggers? That's the easiest and most > > > straightforward solution, I would think. > > > > > -- > > > Until the next mail..., > > > Stefan. > > > > > application_pgp-signature_part > > > < 1KViewDownload > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
