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:
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
The explanation is good:)
thanks for the info, Its really helpful.
muy buena aportación!!!!!! gracias!!!!!!
thanks for the information, Its really helpful
I really like it when folks come together and share ideas.
Great blog, stick with it!
Really nice article
Nice post. I was checking continuously this weblog and I am impressed!
Extremely useful info particularly the closing
I handle such information a lot. I used to be seeking
phase
this particular info for a very long time.
Thank you and best of luck.
but what happen when my Id Field is Primary key with Auto Increment…
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!
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!
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 =)
Nice one and very useful. Thanks
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