Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Inserting to a View – INSTEAD OF TRIGGER – SQL Server

Inserting to a View – INSTEAD OF TRIGGER – SQL Server

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.

However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.

You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

For example, let’s consider following view definition:
– © 2011 – Vishal (http://SqlAndMe.com)
CREATE TABLE [dbo].[Table1]
(      [ID] [INT] NULL,
       [Name] [VARCHAR](20) NULL
)

CREATE TABLE [dbo].[Table2]
(      [ID1] [INT] NULL,
       [Name1] [VARCHAR](20) NULL
)

CREATE View [dbo].[View1]
AS

SELECTTable1.ID, Table1.Name, Table2.Name1
FROM   Table1
INNER JOIN    Table2
              ON Table2.ID1 = Table1.ID

Now, if you try to insert to [View1], you will run into above error. To enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:

– © 2011 – Vishal (http://SqlAndMe.com)
CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Table1
SELECT I.ID, I.Name
FROM INSERTED I

INSERT INTO Table2
SELECT I.ID, I.Name1
FROM INSERTED I
END

That’s all folks. Now, you can execute INSERT statement against the view and it will INSERT the data to the base tables.

INSERT INTO View1 Values (1,'Gandalf','The Grey')

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Verify the base tables:
image

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Raghu G
    January 25, 2012 at 1:29 pm

    The explanation is good:)

  2. August 7, 2012 at 5:17 pm

    thanks for the info, Its really helpful.

  3. lu
    September 13, 2012 at 3:23 am

    muy buena aportación!!!!!! gracias!!!!!!

  4. anil kumar
    September 17, 2012 at 7:03 pm

    thanks for the information, Its really helpful

  5. September 23, 2012 at 7:40 pm

    I really like it when folks come together and share ideas.
    Great blog, stick with it!

  6. Roopa
    September 26, 2012 at 1:04 pm

    Really nice article

  7. Anonymous
    October 14, 2012 at 8:45 pm

    Nice post. I was checking continuously this weblog and I am impressed!

    Extremely useful info particularly the closing
    phase :) I handle such information a lot. I used to be seeking
    this particular info for a very long time.
    Thank you and best of luck.

  8. Hitesh Sharma
    October 16, 2012 at 9:44 am

    but what happen when my Id Field is Primary key with Auto Increment…

  9. wedding planning is easy
    October 17, 2012 at 3:43 pm

    Spot on with this write-up, I actually believe this
    website needs much more attention. I’ll probably be back again to read more,
    thanks for the advice!

  10. Monte
    October 22, 2012 at 9:49 am

    My coder is trying to convince me to move to .

    net from PHP. I have always disliked the idea because of the costs.
    But he’s tryiong none the less. I’ve been using Movable-type on various websites for about a
    year and am nervous about switching to another platform. I have heard excellent things about blogengine.
    net. Is there a way I can transfer all my wordpress posts into
    it? Any kind of help would be really appreciated!

  11. November 4, 2012 at 3:10 pm

    Great article! That is the type of info that should be shared around the internet.

    Disgrace on Google for not positioning this put up upper!
    Come on over and talk over with my website . Thanks =)

  12. Murthy
    February 25, 2013 at 6:47 pm

    Nice one and very useful. Thanks

  13. Paulus121
    May 10, 2013 at 7:25 pm

    It worked like a charm Here is my version of it
    CREATE TRIGGER [dbo].[ItemsQT_NT]
    ON [dbo].[MCG_ITEM]
    INSTEAD OF INSERT
    AS
    BEGIN
    INSERT INTO IT001(CL001CLNO,IT001ITEM,IT001DESC,IT001DESCA,IT001NAME,IT001STAT,IT001FAMI,IT001TYPE,PR001PROD,IT080UNIT)
    SELECT I.COMPANY, I.PRODUCT_CODE, I.ITEM_NAME, I.PRODUCT_ID, I.DESCRIPTION, I.ITEM_STATUS, I.PRODUCT_CODE_2, I.TYPE_ITEM, I.PRODUCT_TYPE, I.UNIT_OF_MESURE
    FROM INSERTED I

    INSERT INTO IT002(IT002NBALERT,IT002DAYQR,CL001CLNO,IT001ITEM)
    SELECT I.DAYS_TO_QUARANTINE_ALERT, I.DAYS_TO_QUARANTINE, I.COMPANY, I.PRODUCT_CODE
    FROM INSERTED I
    END

  1. May 28, 2012 at 7:39 pm

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

Follow

Get every new post delivered to your Inbox.

Join 275 other followers

%d bloggers like this: