I created a view on a table and added an INSTEAD OF TRIGGER to the view.

The idea was to have all access (including data modification)  go through
the view rather than the table.

I tested this concept (or I thought I tested it) and everything seemed to
work fine.

However, I recently discovered that if I attempt to update the view using a
JOIN (e.g., UPDATE vOrders SET x=1 FROM y JOIN z ....), I get the error:

Server: Msg 4422, Level 16, State 1, Line 1
View 'vorders' has an INSTEAD OF UPDATE trigger and cannot be a target of an
UPDATE FROM statement.

A straight UPDATE against the view will work and an INSERT with SELECT FROM
will work but not an UPDATE FROM.

Does anyone know of a work around for my situation?

Or is trying to confine all table access (including data modification) to a
view simply a flawed idea?

Dave
Can you post sample data + expected result that we could test ?


Show quoteHide quote

Thanks guys.

Here is code that demostrates the problem.

Very strange. I found nothing about this limitation in my initial research.

Now I've spent my entire Sunday going through 214 procs and triggers that
reference the credit card tables trying to find those that do an UPDATE FROM
so I can change the code to go against the base table rather than the view.

If anyone has any insights into why an INSTEAD OF trigger operates this way,
or if there is a better work around, I would be grateful for your comments.

Thanks
Dave


USE tempdb
GO

--base table
IF object_id('_orders') IS NOT NULL
    DROP TABLE _orders
CREATE TABLE _orders
    (orderid int
    ,orderdate datetime default getdate()
    ,orderamount money
    ,item varchar(24)
    ,creditcard varchar(24))


INSERT _orders(orderid, orderamount,item,creditcard)
VALUES (1, 10.0, 'aaaa', '1234')



--view
IF object_id('vorders') IS NOT NULL
    DROP VIEW vorders
GO
CREATE VIEW vorders
AS   
    SELECT orderid
    ,orderdate
    ,orderamount
    ,item
    ,'xxxxx' AS creditcard
    FROM _orders


--check
SELECT * FROM vorders
SELECT * FROM _orders



--trigger
IF object_id('T_U_orders') IS NOT NULL
    DROP TRIGGER T_U_orders
GO
CREATE TRIGGER T_U_orders ON vorders
INSTEAD OF INSERT, UPDATE
AS
    IF NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'insert'

        INSERT _orders
            (orderid
            ,orderdate
            ,orderamount
            ,item
            ,creditcard)
        SELECT orderid
            ,orderdate
            ,orderamount
            ,item
            ,creditcard
        FROM inserted
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
        AND EXISTS (SELECT 1 FROM inserted)
    BEGIN
        PRINT 'update'

        UPDATE _orders
        SET orderid=isnull(i.orderid,o.orderid)
            ,orderdate=isnull(i.orderdate,o.orderdate)
            ,orderamount=isnull(i.orderamount,o.orderamount)
            ,item=isnull(i.item,o.item)
            ,creditcard=isnull(i.creditcard,o.creditcard)
        FROM inserted i JOIN _OrdersHistory o
            ON o.orderid=i.orderID

    END


--test basic update
SELECT * FROM _orders

UPDATE vorders
SET creditcard=12
WHERE orderid=1
--SUCCESS
-- update
-- (1 row(s) affected)
-- (1 row(s) affected)

SELECT * FROM vorders
SELECT * FROM _orders



--now test an UPDATE FROM

---create join table
IF object_id('joinTable') IS NOT NULL
    DROP TABLE joinTable
CREATE TABLE joinTable
    (orderid int)

INSERT INTO JoinTable (orderid)
VALUES (1)

--test join
SELECT * FROM vorders v JOIN jointable j ON j.orderid=v.orderid


--now test the UPDATE FROM
UPDATE vorders
SET creditcard=12
FROM vorders v JOIN jointable j ON j.orderid=v.orderid
--FAILS
-- Server: Msg 4422, Level 16, State 1, Line 1
-- View 'vorders' has an INSTEAD OF UPDATE trigger and cannot be a target of
an UPDATE FROM statement.



--try an INSERT FROM
INSERT vorders (orderid, orderamount,item,creditcard)
SELECT j.orderid, 10.0, 'bbbb', '9876'
FROM vorders v JOIN jointable j ON j.orderid=v.orderid
--SUCCESS
-- insert
-- (1 row(s) affected)
-- (1 row(s) affected)

SELECT * FROM vorders v JOIN jointable j ON j.orderid=v.orderid

Instead try this equivalent UPDATE:

UPDATE vorders
SET creditcard = 12
WHERE EXISTS
  (SELECT *
   FROM jointable
   WHERE orderid = vorders.orderid);

Only the computed column is forcing you to use an INSTEAD OF trigger. You
could create a different view for the computed column and then it would be
possible to update this one directly


I suspect this limitation may be a consequence of the logical problems
associated with the UPDATE FROM syntax - specifically that some updates give
unpredictable results. Probably this could cause problems resolving the way
that views get updated but that's just a guess. More discussion in the
following thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/a2027ea842564095

Thanks David

But I am not sure what you mean by "Only the computed column is forcing you
to use an INSTEAD OF trigger."

Which is the computed column?

THe date column has a default constraint but I don't see a "computed" column
in the UPDATE.

Thanks
Dave



Show quoteHide quote

Dave wrote:
Show quoteHide quoteI meant the Creditcard column (actually a constant value rather than
computed - my bad).

If you change the view definition to the following or if you remove
creditcard from the view altogether then you can drop the trigger and
your original UPDATE will work. If you can't do that then try the
UPDATE I posted before.

CREATE VIEW vorders
AS
        SELECT orderid
        ,orderdate
        ,orderamount
        ,item
        ,creditcard
        FROM _orders
GO
DROP TRIGGER T_U_orders

Thanks for your help but I think I am out of luck.

I was trying to avoid changing any proc logic.

What we were trying to do was encrypt creditcard info in the database. There
are over 200 procs that access the creditcard info.  Rather than change all
of them, we decided to rename the creditcard tables and create views with
INSTEAD OF triggers that had the same names of the original tables.  Thus, as
far as the proc was concerned, nothing changed. Now it was going against a
view intead of a table but becase the view had the same name as the original
table, no proc code needed to change.

In order for this to work, the view definition must contain a funcation call
to encypt/decrypt the creditcard number.  The function call is a sub-select
within the view's select list. 

All of this worked fine until I was bitten on the ass by the UPDATE FROM
limitation.

Fortunately (or perhaps unfortunately for my weekend) I was able to go
through all the 200 procs and identify those with UPDATE FROM logic.  There
are not many of them so I won't have to change all of the procs, just a few.

Still, I'm not quite sure why I can perform a straight UPDATE on the
creditcard column (the one defined by the encrypt function in the view
definition) but it chokes if I use a FROM clause with my UPDATE.

Anyway, thanks for your help.

Dave wrote:
Show quoteHide quoteTry an ANSI-style UPDATE (with a subquery) instead of the UPDATE FROM:

UPDATE vOrders
SET x =
(SELECT ...
  FROM y
  WHERE y.z = vOrders.z ... etc
)
WHERE ... ;

Updateable views are OK. But many views are updateable without using an
INSTEAD OF trigger. Don't use the trigger unless you have to. Example:

CREATE TABLE orders (x INTEGER NOT NULL PRIMARY KEY, y INTEGER NOT
NULL);
GO
CREATE VIEW vOrders
AS
SELECT x,y
  FROM orders
GO

INSERT INTO vOrders (x,y)
VALUES (1,2);

UPDATE vOrders SET y = 3;

SELECT x,y FROM vOrders;

Hope this helps.

Order By Primary Key
Select where result are by the best match
where current of cursorName
Really really slow cursor
Problem copying data from column in same table