Yes, it would. You may want to take a look at IInterceptor.PrepareSQL, for a case by case change.
On Sat, Oct 4, 2008 at 12:08 AM, MAMMON <[EMAIL PROTECTED]> wrote: > > Wouldn't that cause @@IDENTITY to be used in ALL cases? Not just the > classes mapped to views with INSTEAD OF INSERT triggers? > > On Oct 3, 2:02 pm, "Ayende Rahien" <[EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
