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
-~----------~----~----~----~------~----~------~--~---
