Systems Engineering and RDBMS

More on Updatable Views and Instead of Triggers

Posted by decipherinfosys on April 9, 2009

In response to yesterday’s blog post on updatable views and Instead Of Triggers (Oracle), a reader asked us to provide a similar example for SQL Server as well.  We will cover that in today’s post.  Let’s take the same example as we did for that post:


CREATE TABLE PRODUCT
(
PRODUCT_ID INT NOT NULL,
PRODUCT_NAME VARCHAR(100) NOT NULL,
STYLE VARCHAR(10),
COLOR VARCHAR(10),
FLAMMABLE CHAR(1),
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
)
GO
SET NOCOUNT ON
GO


INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(1,'CANDLES','REGULAR','BLUE','Y')
GO
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(2,'FRAMES','GOTHIC','SILVER','N')
GO
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(3,'GLASSES','MODERN','RED','N')
GO
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(4,'DISHES','PLAIN','WHITE','N')
GO

-- View
IF EXISTS (SELECT * FROM sysobjects where xtype = 'V' and name = 'VW_PRODUCT')
BEGIN
DROP VIEW VW_PRODUCT
END
GO
CREATE VIEW VW_PRODUCT
AS
SELECT PRODUCT_ID,'STYLE' AS ATTRIBUTE,STYLE as [VALUE]
FROM PRODUCT
UNION ALL
SELECT PRODUCT_ID,'COLOR',COLOR
FROM PRODUCT
UNION ALL
SELECT PRODUCT_ID,'FLAMMABLE',FLAMMABLE
FROM PRODUCT
GO

Now, let’s try to update this view directly:

UPDATE VW_PRODUCT
SET VALUE = ‘N’
WHERE PRODUCT_ID = 1
AND ATTRIBUTE = ‘FLAMMABLE’
GO

And we will get an error:

Msg 4442, Level 16, State 7, Line 1
UNION ALL view ‘VW_PRODUCT’ is not updatable because base table ‘[PRODUCT]’ is used multiple times.

Let’s create a simple instead of trigger now:

IF EXISTS (SELECT * FROM sysobjects where xtype = 'TR' and name = 'IOF_VW_PRODUCT')
BEGIN
DROP TRIGGER IOF_VW_PRODUCT
END
GO
CREATE TRIGGER IOF_VW_PRODUCT ON VW_PRODUCT
INSTEAD OF UPDATE
AS
BEGIN
UPDATE PRODUCT
SET FLAMMABLE = i.value
FROM product p, inserted i
WHERE p.PRODUCT_ID = i.product_id

END
GO

And now, let’s execute the same update statement against the view:

UPDATE VW_PRODUCT
SET VALUE = 'N'
WHERE PRODUCT_ID = 1
AND ATTRIBUTE = 'FLAMMABLE'
GO

And this time, it will go through and make the right update.  Another thing to note is that Instead of triggers are also allowed on tables.  One common mistake is that people think that triggers (not just instead of triggers but regular ones as well) fire once for each row updated and that the inserted/deleted tables will have one entry.  Other than recursive triggers, the triggers will fire once per SQL statement and those magic tables (inserted/deleted) will have all the rows effected by that statement.

Resources:

  • BOL – here.
  • Discussion on the MSDN forums – here and here.
  • Using inserted and deleted tables – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: